• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

unique fill value|General Excel Questions & Answers|Excel Forum|My Online Training Hub

You are here: Home / unique fill value|General Excel Questions & Answers|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumGeneral Excel Questions & Answe…unique fill value
sp_PrintTopic sp_TopicIcon
unique fill value
Avatar
Deepak bhanushali
Member
Members
Level 0
Forum Posts: 7
Member Since:
June 4, 2020
sp_UserOfflineSmall Offline
1
June 5, 2020 - 12:11 am
sp_Permalink sp_Print

I have created 6 row

BILL NO.           ITEM NAME             ITEM VALUE    Count Bill No      Count Item      Sum Value

123                   AAA                          50                  1                      1                 100

123                   AAA                          50

123                   BB                            50                                          2

133                   AA                            50                  2                       1                50 

133                   AA                            50

134                   AA                           50                   3                       1               50

1. Fill Series of (Count Item Column) with 1,2,3 till bill no is same also it should look for only unique item name,  duplicate should not be counted

2. if Bill No. Changes then (Count Item Column) fill series should start from 1

3. I want to sum amount but criteria will Bill No wise plus unique Item Name (it item is repeated in same bill no it should not be valued in sum)

Avatar
Purfleet
England
Member
Members


Trusted Members
Level 4
Forum Posts: 412
Member Since:
December 20, 2019
sp_UserOfflineSmall Offline
2
June 5, 2020 - 3:44 am
sp_Permalink sp_Print

Please up load an example workbook rather than paste in text, it makes it much easier to review

Purfleet

Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1514
Member Since:
October 5, 2010
sp_UserOfflineSmall Offline
3
June 5, 2020 - 9:14 am
sp_Permalink sp_Print

Hi Deepak,

Please refer to this https://www.myonlinetraininghu.....this-first

Please supply a clear explanation of your problem and include a workbook and some examples of the expected result(s). From your initial post I am not clear at all on what you are after or how to implement a solution.

Regards

Phil

Avatar
Deepak bhanushali
Member
Members
Level 0
Forum Posts: 7
Member Since:
June 4, 2020
sp_UserOfflineSmall Offline
4
June 5, 2020 - 7:59 pm
sp_Permalink sp_Print sp_EditHistory

1.In Col E if I have four bill No mention in column B then it should give result in col E with unique No. as mention below

2. In Col F it should serial No.( items in a bill No.) and if bill no changes then it should start with 1

3 In Col G it should sum amount of only Bill Wise (col A) Unique Item wise (column B) and give total in col G

Hope you will be able to understand my problem now

S.N.
(Column A)
B NO.
Column B)
Item Code
(Column C)
Item Amount
(Column D)
Col E (Count of Col B and Fill Series) Col F Col G Col H
1 2615903 MRKU55 18188 2240 1       1 4240  
2 2615903 MRKU55 18188 2240        
3 2615903 MRKU55 18188 2240        
4 2615903 MRSU39 74448 2000         2    
5 2615903 MRSU39 74448 2100        
6 2615903 MRSU39 74448 2100        
7 2622592 TCNU28 62226 2300 2      1 2300  
8 2622592 TCNU28 62226 2300        
9 2968613 MSKU97 81297 2240 3       1 2240  
10 2968613 MSKU97 81297 2240        
11 2968613 MSKU97 81297 2240        
12 2968613 MSKU97 81297 2240        
13 2968613 MSKU97 81297 2240        
14 3136549 SUDU68 44585 2500 4       1 2500  
15 3136549 SUDU68 44585 2500        
Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1514
Member Since:
October 5, 2010
sp_UserOfflineSmall Offline
5
June 5, 2020 - 9:53 pm
sp_Permalink sp_Print

Hi Deepak,

Without your workbook we have to enter everything by hand.  That not only takes time, it makes things prone to error, and it means we are entering data into rows, columns, sheets, tables etc which probably do not accurately reflect your own data.

Which means that when we come up with a solution, it will probably have to be modified to fit your exact situation.

Our time is limited so, can you please attach a workbook with your data as this will mean we can devote more time to helping you find a solution and be able to help others too.  

Phil

Avatar
Deepak bhanushali
Member
Members
Level 0
Forum Posts: 7
Member Since:
June 4, 2020
sp_UserOfflineSmall Offline
6
June 6, 2020 - 1:11 am
sp_Permalink sp_Print sp_EditHistory
Avatar
Purfleet
England
Member
Members


Trusted Members
Level 4
Forum Posts: 412
Member Since:
December 20, 2019
sp_UserOfflineSmall Offline
7
June 6, 2020 - 7:34 pm
sp_Permalink sp_Print sp_EditHistory

So you want column W to display the total of column O multiplied by the number in V?

If so try =IF($V2<>"",SUMPRODUCT(--($V$2:$V$16<>"")*($O$2:$O$16)*($A$2:$A$16=$A2)),"")

Purfleet

Avatar
Deepak bhanushali
Member
Members
Level 0
Forum Posts: 7
Member Since:
June 4, 2020
sp_UserOfflineSmall Offline
8
June 6, 2020 - 10:23 pm
sp_Permalink sp_Print sp_EditHistory

Thanks sir for the reply

now this is actually I wanted

1. Column A consist bill no and column G consist item So, it will me give me unique Serial of both (Column A and Column G) in Column V

i.e A2 value is from A2 to A7 and in column G it has found only 2 unique value  in G2 and G5 so column V2 value is 1 and column V5 value is 2

A8 value is from A8 to A9 and in column G it has found only 1 unique value in G8 so column V8 value is 1

A10 value is from A10 to A14 and in column G it has found only 1 unique value G10 so column V10  value is 1

A15 value is from A15 to A16 and in column G it has found only 1 unique value G15 so column V14 value is 1

 

2 Column W will sum only column O of the unique value Column A and column G

i.e A2 value is from A2 to A7 and in column G it has found only 2 unique value in G2 and G5 so it will sum value mention in O2+O5 in column W1 "Rs.44800

A8 value is from A8 to A9 and in column G it has found only 1 unique value in G8 so it will sum value mention in O8 in column W8 "Rs.22400

A10 value is from A10 to A14 and in column G it has found only 1 unique value in G10 so it will sum value mention in O10 in column W10 "Rs.22400

A15 value is from A15 to A16 and in column G it has found only 1 unique value in A15 so it will sum value mention in O15 in column W15 "Rs.22400

Avatar
Purfleet
England
Member
Members


Trusted Members
Level 4
Forum Posts: 412
Member Since:
December 20, 2019
sp_UserOfflineSmall Offline
9
June 7, 2020 - 7:03 am
sp_Permalink sp_Print

I thought that is what i done, except the total showed where there was any number (so 2645903 showed at 1 & 2 (rows 2 & 5).

If you only want it showing at the 1's you can change the formula to

=IF($V2=1,SUMPRODUCT(--($V$2:$V$16<>"")*($O$2:$O$16)*($A$2:$A$16=$A2)),"")

Is that it?

Purfleet

Avatar
Deepak bhanushali
Member
Members
Level 0
Forum Posts: 7
Member Since:
June 4, 2020
sp_UserOfflineSmall Offline
10
June 7, 2020 - 4:44 pm
sp_Permalink sp_Print

Yes got the 2nd point answer but what about 1st point

1. Column A consist bill no and column G consist item So, it will me give me unique Serial of both (Column A and Column G) in Column V

i.e A2 value is from A2 to A7 and in column G it has found only 2 unique value  in G2 and G5 so column V2 value is 1 and column V5 value is 2

A8 value is from A8 to A9 and in column G it has found only 1 unique value in G8 so column V8 value is 1

A10 value is from A10 to A14 and in column G it has found only 1 unique value G10 so column V10  value is 1

A15 value is from A15 to A16 and in column G it has found only 1 unique value G15 so column V14 value is 1

Avatar
Anders Sehlstedt
Eskilstuna, Sweden

VIP
Members


Trusted Members
Level 3
Forum Posts: 870
Member Since:
December 7, 2016
sp_UserOfflineSmall Offline
11
June 9, 2020 - 12:43 am
sp_Permalink sp_Print

Hello Deepak,

In order to keep things simple I have added a helper column as a base to count unique TEU per SB NO.
See attached file (a modified copy of the file Purfleet uploaded).

Br,
Anders

The following users say thank you to Anders Sehlstedt for this useful post:

Purfleet
Avatar
Deepak bhanushali
Member
Members
Level 0
Forum Posts: 7
Member Since:
June 4, 2020
sp_UserOfflineSmall Offline
12
June 9, 2020 - 7:54 pm
sp_Permalink sp_Print sp_EditHistory

Great Sir Mr.Anders for the help its working

Avatar
Anders Sehlstedt
Eskilstuna, Sweden

VIP
Members


Trusted Members
Level 3
Forum Posts: 870
Member Since:
December 7, 2016
sp_UserOfflineSmall Offline
13
June 10, 2020 - 1:00 am
sp_Permalink sp_Print

Great, glad I could give some help.

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Chandler Davis
Guest(s) 8
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 870
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
michael serna
mashal sana
Tiffany Kang
Leah Gillmore
Sopi Yuniarti
LAFONSO HERNANDEZ
Hayden Hao
Angela chen
Sean Moore
John Chisholm
Forum Stats:
Groups: 3
Forums: 24
Topics: 6215
Posts: 27245

 

Member Stats:
Guest Posters: 49
Members: 31897
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.