Selection of specific information from data sheet

Zubair

Active Member
Joined
Jul 4, 2009
Messages
299
Office Version
  1. 2016
Platform
  1. Windows
Hi Experts,

I want many different sets of information from the data sheet given below data for example I want to bring Mar Volume and Gross Sales for Customer 199 & 200 as shown below in the result sheet. Please share formula no VBA.

Similarly based on headings shown below another example, so formula should based on headings.

Data Sheet
YearYearMMMonthYTD No.Customer #Customer NameRegion CodeRegion NameRegional ManagerArea CodeArea NameArea ManagerItem #SKUGradeSegmentVolumeGross sales
2021​
A202101Jan
90​
1000298​
Customer 198ZRC5Region 5Regional Manager 5ZRAC21Area 21Area Manager 21PKG11010208ArfJet7 208 ltrArfSegment 31,144626,912
2021​
A202101Jan
90​
1000299​
Customer 199ZRC5Region 5Regional Manager 5ZRAC21Area 21Area Manager 21PKG11010208ArfJet7 208 ltrArfSegment 3986540,328
2021​
A202101Jan
90​
1000300​
Customer 200ZRC5Region 5Regional Manager 5ZRAC21Area 21Area Manager 21PKG11010208ArfJet7 208 ltrArfSegment 3364199,472
2021​
A202101Jan
90​
1000341​
Customer 241ZRC5Region 5Regional Manager 5ZRAC25Area 25Area Manager 25PKG11010208ArfJet7 208 ltrArfSegment 3211115,628
2021​
A202101Jan
90​
1000342​
Customer 242ZRC5Region 5Regional Manager 5ZRAC25Area 25Area Manager 25PKG11010208ArfJet7 208 ltrArfSegment 3798437,304
2021​
A202101Jan
90​
1000343​
Customer 243ZRC5Region 5Regional Manager 5ZRAC25Area 25Area Manager 25PKG11010208ArfJet7 208 ltrArfSegment 1388212,624
2021​
A202101Jan
90​
1000344​
Customer 244ZRC5Region 5Regional Manager 5ZRAC25Area 25Area Manager 25PKG11010208ArfJet7 208 ltrArfSegment 41,263692,124
2021​
A202101Jan
90​
1000345​
Customer 245ZRC5Region 5Regional Manager 5ZRAC25Area 25Area Manager 25PKG11010208ArfJet7 208 ltrArfSegment 2852466,896
2021​
A202101Jan
90​
1000346​
Customer 246ZRC5Region 5Regional Manager 5ZRAC25Area 25Area Manager 25PKG11010208ArfJet7 208 ltrArfSegment 5652357,296
2021​
A202101Jan
90​
1000347​
Customer 247ZRC5Region 5Regional Manager 5ZRAC25Area 25Area Manager 25PKG11010208ArfJet7 208 ltrArfSegment 4333182,484
2021​
A202101Jan
90​
1000348​
Customer 248ZRC5Region 5Regional Manager 5ZRAC25Area 25Area Manager 25PKG11010208ArfJet7 208 ltrArfSegment 31,084594,032
2021​
A202101Jan
90​
1000349​
Customer 249ZRC5Region 5Regional Manager 5ZRAC25Area 25Area Manager 25PKG11010208ArfJet7 208 ltrArfSegment 41,082592,936
2021​
A202101Jan
90​
1000350​
Customer 250ZRC5Region 5Regional Manager 5ZRAC25Area 25Area Manager 25PKG11010208ArfJet7 208 ltrArfSegment 5503275,644
2021​
A202102Feb
90​
1000298​
Customer 198ZRC5Region 5Regional Manager 5ZRAC21Area 21Area Manager 21PKG11010208ArfJet7 208 ltrArfSegment 32,7041,498,016
2021​
A202102Feb
90​
1000299​
Customer 199ZRC5Region 5Regional Manager 5ZRAC21Area 21Area Manager 21PKG11010208ArfJet7 208 ltrArfSegment 12,3181,284,172
2021​
A202102Feb
90​
1000300​
Customer 200ZRC5Region 5Regional Manager 5ZRAC21Area 21Area Manager 21PKG11010208ArfJet7 208 ltrArfSegment 1894495,276
2021​
A202102Feb
90​
1000341​
Customer 241ZRC5Region 5Regional Manager 5ZRAC25Area 25Area Manager 25PKG11010208ArfJet7 208 ltrArfSegment 3664367,856
2021​
A202102Feb
90​
1000342​
Customer 242ZRC5Region 5Regional Manager 5ZRAC25Area 25Area Manager 25PKG11010208ArfJet7 208 ltrArfSegment 12,7501,523,500
2021​
A202102Feb
90​
1000343​
Customer 243ZRC5Region 5Regional Manager 5ZRAC25Area 25Area Manager 25PKG11010208ArfJet7 208 ltrArfSegment 41,388768,952
2021​
A202102Feb
90​
1000344​
Customer 244ZRC5Region 5Regional Manager 5ZRAC25Area 25Area Manager 25PKG11010208ArfJet7 208 ltrArfSegment 24,3082,386,632
2021​
A202102Feb
90​
1000345​
Customer 245ZRC5Region 5Regional Manager 5ZRAC25Area 25Area Manager 25PKG11010208ArfJet7 208 ltrArfSegment 53,1931,768,922
2021​
A202102Feb
90​
1000346​
Customer 246ZRC5Region 5Regional Manager 5ZRAC25Area 25Area Manager 25PKG11010208ArfJet7 208 ltrArfSegment 32,6231,453,142
2021​
A202102Feb
90​
1000347​
Customer 247ZRC5Region 5Regional Manager 5ZRAC25Area 25Area Manager 25PKG11010208ArfJet7 208 ltrArfSegment 21,488824,352
2021​
A202102Feb
90​
1000348​
Customer 248ZRC5Region 5Regional Manager 5ZRAC25Area 25Area Manager 25PKG11010208ArfJet7 208 ltrArfSegment 14,5432,516,822
2021​
A202102Feb
90​
1000349​
Customer 249ZRC5Region 5Regional Manager 5ZRAC25Area 25Area Manager 25PKG11010208ArfJet7 208 ltrArfSegment 24,2072,330,678
2021​
A202102Feb
90​
1000350​
Customer 250ZRC5Region 5Regional Manager 5ZRAC25Area 25Area Manager 25PKG11010208ArfJet7 208 ltrArfSegment 31,8951,049,830
2021​
A202103Mar
90​
1000298​
Customer 198ZRC5Region 5Regional Manager 5ZRAC21Area 21Area Manager 21PKG11010208ArfJet7 208 ltrArfSegment 22,4701,407,900
2021​
A202103Mar
90​
1000299​
Customer 199ZRC5Region 5Regional Manager 5ZRAC21Area 21Area Manager 21PKG11010208ArfJet7 208 ltrArfSegment 52,1441,222,080
2021​
A202103Mar
90​
1000300​
Customer 200ZRC5Region 5Regional Manager 5ZRAC21Area 21Area Manager 21PKG11010208ArfJet7 208 ltrArfSegment 5834475,380
2021​
A202103Mar
90​
1000341​
Customer 241ZRC5Region 5Regional Manager 5ZRAC25Area 25Area Manager 25PKG11010208ArfJet7 208 ltrArfSegment 2401228,570
2021​
A202103Mar
90​
1000342​
Customer 242ZRC5Region 5Regional Manager 5ZRAC25Area 25Area Manager 25PKG11010208ArfJet7 208 ltrArfSegment 51,665949,050
2021​
A202103Mar
90​
1000343​
Customer 243ZRC5Region 5Regional Manager 5ZRAC25Area 25Area Manager 25PKG11010208ArfJet7 208 ltrArfSegment 3837477,090

Result.
YearMonthCustomer #Customer NameArea NameArea ManagerItem #SKUVolumeGross sales
A2021Mar
1000299​
Customer 199Area 21Area Manager 21PKG11010208ArfJet7 208 ltr2,1441,222,080
A2021Mar
1000300​
Customer 200Area 21Area Manager 21PKG11010208ArfJet7 208 ltr834475,380

Another example
YearMonthCustomer #Customer NameRegional ManagerArea CodeArea NameArea ManagerGradeSegmentVolumeGross sales
A2021Mar
1000298​
Customer 198Regional Manager 5ZRAC21Area 21Area Manager 21ArfSegment 22,4701,407,900
A2021Mar
1000341​
Customer 241Regional Manager 5ZRAC25Area 25Area Manager 25ArfSegment 2401228,570
A2021Mar
1000346​
Customer 246Regional Manager 5ZRAC25Area 25Area Manager 25ArfSegment 21,415806,550
A2021Mar
1000350​
Customer 250Regional Manager 5ZRAC25Area 25Area Manager 25ArfSegment 2993566,010
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
You should Input Year, Month & Customer# for each row.
How about:
Book1.xlsx
ABCDEFGHIJKLMNOPQRS
1YearYearMMMonthYTD No.Customer #Customer NameRegion CodeRegion NameRegional ManagerArea CodeArea NameArea ManagerItem #SKUGradeSegmentVolumeGross sales
22021A20211Jan901000298Customer 198ZRC5Region 5Regional Manager 5ZRAC21Area 21Area Manager 21PKG11010208ArfJet7 208 ltrArfSegment 31,144626,912
32021A20211Jan901000299Customer 199ZRC5Region 5Regional Manager 5ZRAC21Area 21Area Manager 21PKG11010208ArfJet7 208 ltrArfSegment 3986540,328
42021A20211Jan901000300Customer 200ZRC5Region 5Regional Manager 5ZRAC21Area 21Area Manager 21PKG11010208ArfJet7 208 ltrArfSegment 3364199,472
52021A20211Jan901000341Customer 241ZRC5Region 5Regional Manager 5ZRAC25Area 25Area Manager 25PKG11010208ArfJet7 208 ltrArfSegment 3211115,628
62021A20211Jan901000342Customer 242ZRC5Region 5Regional Manager 5ZRAC25Area 25Area Manager 25PKG11010208ArfJet7 208 ltrArfSegment 3798437,304
72021A20211Jan901000343Customer 243ZRC5Region 5Regional Manager 5ZRAC25Area 25Area Manager 25PKG11010208ArfJet7 208 ltrArfSegment 1388212,624
82021A20211Jan901000344Customer 244ZRC5Region 5Regional Manager 5ZRAC25Area 25Area Manager 25PKG11010208ArfJet7 208 ltrArfSegment 41,263692,124
92021A20211Jan901000345Customer 245ZRC5Region 5Regional Manager 5ZRAC25Area 25Area Manager 25PKG11010208ArfJet7 208 ltrArfSegment 2852466,896
102021A20211Jan901000346Customer 246ZRC5Region 5Regional Manager 5ZRAC25Area 25Area Manager 25PKG11010208ArfJet7 208 ltrArfSegment 5652357,296
112021A20211Jan901000347Customer 247ZRC5Region 5Regional Manager 5ZRAC25Area 25Area Manager 25PKG11010208ArfJet7 208 ltrArfSegment 4333182,484
122021A20211Jan901000348Customer 248ZRC5Region 5Regional Manager 5ZRAC25Area 25Area Manager 25PKG11010208ArfJet7 208 ltrArfSegment 31,084594,032
132021A20211Jan901000349Customer 249ZRC5Region 5Regional Manager 5ZRAC25Area 25Area Manager 25PKG11010208ArfJet7 208 ltrArfSegment 41,082592,936
142021A20211Jan901000350Customer 250ZRC5Region 5Regional Manager 5ZRAC25Area 25Area Manager 25PKG11010208ArfJet7 208 ltrArfSegment 5503275,644
152021A20212Feb901000298Customer 198ZRC5Region 5Regional Manager 5ZRAC21Area 21Area Manager 21PKG11010208ArfJet7 208 ltrArfSegment 32,7041,498,016
162021A20212Feb901000299Customer 199ZRC5Region 5Regional Manager 5ZRAC21Area 21Area Manager 21PKG11010208ArfJet7 208 ltrArfSegment 12,3181,284,172
172021A20212Feb901000300Customer 200ZRC5Region 5Regional Manager 5ZRAC21Area 21Area Manager 21PKG11010208ArfJet7 208 ltrArfSegment 1894495,276
182021A20212Feb901000341Customer 241ZRC5Region 5Regional Manager 5ZRAC25Area 25Area Manager 25PKG11010208ArfJet7 208 ltrArfSegment 3664367,856
192021A20212Feb901000342Customer 242ZRC5Region 5Regional Manager 5ZRAC25Area 25Area Manager 25PKG11010208ArfJet7 208 ltrArfSegment 12,7501,523,500
202021A20212Feb901000343Customer 243ZRC5Region 5Regional Manager 5ZRAC25Area 25Area Manager 25PKG11010208ArfJet7 208 ltrArfSegment 41,388768,952
212021A20212Feb901000344Customer 244ZRC5Region 5Regional Manager 5ZRAC25Area 25Area Manager 25PKG11010208ArfJet7 208 ltrArfSegment 24,3082,386,632
222021A20212Feb901000345Customer 245ZRC5Region 5Regional Manager 5ZRAC25Area 25Area Manager 25PKG11010208ArfJet7 208 ltrArfSegment 53,1931,768,922
232021A20212Feb901000346Customer 246ZRC5Region 5Regional Manager 5ZRAC25Area 25Area Manager 25PKG11010208ArfJet7 208 ltrArfSegment 32,6231,453,142
242021A20212Feb901000347Customer 247ZRC5Region 5Regional Manager 5ZRAC25Area 25Area Manager 25PKG11010208ArfJet7 208 ltrArfSegment 21,488824,352
252021A20212Feb901000348Customer 248ZRC5Region 5Regional Manager 5ZRAC25Area 25Area Manager 25PKG11010208ArfJet7 208 ltrArfSegment 14,5432,516,822
262021A20212Feb901000349Customer 249ZRC5Region 5Regional Manager 5ZRAC25Area 25Area Manager 25PKG11010208ArfJet7 208 ltrArfSegment 24,2072,330,678
272021A20212Feb901000350Customer 250ZRC5Region 5Regional Manager 5ZRAC25Area 25Area Manager 25PKG11010208ArfJet7 208 ltrArfSegment 31,8951,049,830
282021A20213Mar901000298Customer 198ZRC5Region 5Regional Manager 5ZRAC21Area 21Area Manager 21PKG11010208ArfJet7 208 ltrArfSegment 22,4701,407,900
292021A20213Mar901000299Customer 199ZRC5Region 5Regional Manager 5ZRAC21Area 21Area Manager 21PKG11010208ArfJet7 208 ltrArfSegment 52,1441,222,080
302021A20213Mar901000300Customer 200ZRC5Region 5Regional Manager 5ZRAC21Area 21Area Manager 21PKG11010208ArfJet7 208 ltrArfSegment 5834475,380
312021A20213Mar901000341Customer 241ZRC5Region 5Regional Manager 5ZRAC25Area 25Area Manager 25PKG11010208ArfJet7 208 ltrArfSegment 2401228,570
322021A20213Mar901000342Customer 242ZRC5Region 5Regional Manager 5ZRAC25Area 25Area Manager 25PKG11010208ArfJet7 208 ltrArfSegment 51,665949,050
332021A20213Mar901000343Customer 243ZRC5Region 5Regional Manager 5ZRAC25Area 25Area Manager 25PKG11010208ArfJet7 208 ltrArfSegment 3837477,090
34
35YearMonthCustomer #Customer NameArea NameArea ManagerItem #SKUVolumeGross sales
36A2021Mar1000299Customer 199Area 21Area Manager 21PKG11010208ArfJet7 208 ltr21441,222,080
37A2021Mar1000300Customer 200Area 21Area Manager 21PKG11010208ArfJet7 208 ltr834475,380
38
39YearMonthCustomer #Customer NameRegional ManagerArea CodeArea NameArea ManagerGradeSegmentVolumeGross sales
40A2021Mar1000298Customer 198Regional Manager 5ZRAC21Area 21Area Manager 21ArfSegment 224701,407,900
41A2021Mar1000300Customer 200Regional Manager 5ZRAC21Area 21Area Manager 21ArfSegment 5834475,380
42A2021Mar1000341Customer 241Regional Manager 5ZRAC25Area 25Area Manager 25ArfSegment 2401228,570
43A2021Mar1000342Customer 242Regional Manager 5ZRAC25Area 25Area Manager 25ArfSegment 51665949,050
44A2021Mar1000343Customer 243Regional Manager 5ZRAC25Area 25Area Manager 25ArfSegment 3837477,090
45
Sheet2
Cell Formulas
RangeFormula
D36:J37D36=INDEX($A$2:$S$33,MATCH(1,($B$2:$B$33=$A36)*($D$2:$D$33=$B36)*($F$2:$F$33=$C36),0),MATCH(D$35,$A$1:$S$1,0))
D40:L44D40=INDEX($A$2:$S$33,MATCH(1,($B$2:$B$33=$A40)*($D$2:$D$33=$B40)*($F$2:$F$33=$C40),0),MATCH(D$39,$A$1:$S$1,0))
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
Solution
Many thanks but I am getting #VALUE!

YearMonthCustomer #Customer NameArea NameArea ManagerItem #SKUVolumeGross sales
A2021Mar1000299#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!
A2021Mar1000300#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!
YearMonthCustomer #Customer NameRegional ManagerArea CodeArea NameArea ManagerGradeSegmentVolumeGross sales
A2021Mar1000298#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!
#VALUE!​
#VALUE!​
A2021Mar1000300#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!
#VALUE!​
#VALUE!​
A2021Mar1000341#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!
#VALUE!​
#VALUE!​
A2021Mar1000342#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!
#VALUE!​
#VALUE!​
A2021Mar1000343#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!
#VALUE!​
#VALUE!​
 
Upvote 0
You're Welcome & Thanks for Feedback.
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,315
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top