Vlookup or some other alternative.

PFS12

New Member
Joined
Jan 28, 2014
Messages
33
Office Version
  1. 2016
Platform
  1. Windows
Afternoon,

I am running a sales contest in which I have to pull part numbers, and specially coded numbers out of a list and total them by salesmen. My data comes to me like this:



SMITH061
2001368​
02JRIP214984 ROW CLUTCH IN
20200501​
20200501​
540​
180​
AGLE01
720​
4​
AGSAGS
SMITH061400162402JRIP21498CLUTCH, SURE ST
20200501​
20200501​
3840​
320​
AGLE01
5120​
16​
AGSAGS
SMITH0617336853202JRIP21498HARNESS
20200501​
20200501​
78.4​
98​
CASE07
107.8​
1​
66095660


I need to distinguish parts sales for the JRI salesmen by two different ways. The first way is to pull the part number direct. which is column 2 of the data above. I have a database with all the "qualifying" part numbers on it in a different sheet. (See Below). The second way is to sum the parts totals in column 13 by the marker "66095" in column 15. I can filter my sheet and capture the data by salesmen using the "66095" code which isn't a big deal, but I don't have a way to individually mark the part numbers in column 2 to make them unique, so I was looking for a "one stop shop". I can probably get away with a long SUMIF formula, but there has to be a better way. and I am not super familiar with vlookup, and my version of excel has not updated to contain xlookup yet.

In the example I provided, the JRI salesmen should have 827.80 for his total.

Part Number
2001368​
2006036-840
2006036-ENG
2006506-ENG
2006507-ENG
2006508-ENG
2006509-ENG

Thank you so much in advance for anyone willing to tackle this for me.


Seth
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,007
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
1. Would be really helpful if you had placed headers on the columns so we had a better understanding of what is what.
2. A mocked up solution of what you want based upon the data presented will help also.
3. Use of XL2BB to upload the data will allow us to recreate your issue on our PCs without having to re-type all your data and make assumptions as to format.
4. With your example, I don't see any relationship between the two tables. Care to clarify?


Do you want to try again with those suggestions? Also, what version of Excel are you operating. That will make a difference in the solution provided. Suggest you update your profile to indicate which version of Excel you have.
 

PFS12

New Member
Joined
Jan 28, 2014
Messages
33
Office Version
  1. 2016
Platform
  1. Windows
I am sorry, I can't add that extension in as my Excel (version 2016) is run through my IT department at work and I can't use any add ins so I'll try my best.

Here is a copy of a table that I will fill with data monthly in the same exact order as it is here. Its starts at the corner of the sheet with Column A being PO#/Customer #, ending with Column P being the Class

ABCDEFGHIJKLMNOP
PO # / Cust #:Part Number:BrSalesman #ResponsibleVendor / Invoice #:Description:Invoice OpenBilling DateAverage CostPrice:Vendor #Sell Price:Qty:Model:Class:
SMITH061
2001368​
02JRIP214984 ROW CLUTCH IN
20200501​
20200501​
540​
180​
AGLE01
720​
4​
AGSAGS
SMITH061400162402JRIP21498CLUTCH, SURE ST
20200501​
20200501​
3840​
320​
AGLE01
5120​
16​
AGSAGS
SMITH0617336853202JRIP21498HARNESS
20200501​
20200501​
78.4​
98​
CASE07
107.8​
1​
66095660

-The data that I want is in column M (Sell Price)

The data is based on two seperate groups, Specific part numbers from a different table, and a code in the model field.
-There will be different Salesmen in the salesman column, abbreviated by their 3 letter code.
-There are some parts coded with Model # 66095 that I want to sort out of this table.
-There are specific part numbers in the part number column that I want to choose from a file on a seperate tab in the worksheet. That is the second example of data that I have attached below, in just one column (A)

A
Part Number
2001368​
2006036-840
2006036-ENG
2006506-ENG
2006507-ENG
2006508-ENG
2006509-ENG
2006510-ENG
2006511-ENG

Ultimately what I need is to have the salesmen name, how much 66095 did they sell and how much of the selected part numbers did they sell.

I envision the table to look like below

ABCD
part numbers66095Total
JRI720107.80=SUM(B2+C2) Should equal 827.80
NWO
GAR
NMO

I'm sorry if this still doesn't make any sense. Thats why Im here!
 

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,007
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Look at this file to see the Power Query Solution. It is located on Box.Net. There are several Tabs that are created by PQ as tables are adjusted and saved. If you update your two original tables and then click on the Refresh button on the Data tab 2 times, it will update the final report tab with your expected information based upon the sample you provided.

 

PFS12

New Member
Joined
Jan 28, 2014
Messages
33
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Alan thank you for this first step. I was able to update the part numbers and the data in the sales and the PQ tables updated to accommodate, but the filters and final results stop working after that. I certainly could be doing something wrong. I copied the rest of the part numbers into the sheet and copied additional information into the sales sheet, but after hitting refresh any results you had disappeared. There are more salesmen to track, and I would be uploading sales data from Now until the end of the year so I would need this to be able to expand to more sales data, the rest would be relatively static.

Thanks again
 

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,007
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
If you load your file (two tables as they currently are (make sure to dummy any data that needs to be) and load to Box.Net. Post the link here and I will try to build you a parameter query that will let you search by Salesman and particular products.
 

PFS12

New Member
Joined
Jan 28, 2014
Messages
33
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Thank you anyway!
 

PFS12

New Member
Joined
Jan 28, 2014
Messages
33
Office Version
  1. 2016
Platform
  1. Windows
Here is my Database for specific part numbers (Ag Leader):

Planting Parts Contest File.xlsx
A
1Part Number
22001368
32006036-840
42006036-ENG
52006506-ENG
62006507-ENG
72006508-ENG
82006509-ENG
92006510-ENG
102006511-ENG
112006512-ENG
122006513-ENG
13201-0404-01
144001011-15
154001011-25
164001011-6
174001302
184001303-1
194002003
204002036
214002041
224002142-20
234002151-1
244002207
254002221
264002222
274002231-6
284002463
294002506-12
304002563
31200-0454-03
32201-0504-03
33201-0515-01
344001385
354001403-6
364001454
374002570
384002584-16
394002584-22
404002584-8
414002585-10
424002585-18
434002587
444002591-25
454002591-30
464002597-008
474002598-10
484002598-14
494002598-25
504002599-25
514002601-25
524002602-25
534002630
544002631
554002694-4
564002724-6
574002725-6
584002730-4
594002733
604002736
614003478
624003479
634003480
644003481
654003492
664003493
674003496
684003497
694003498
704003499
714003591-3
724003610-15
734003610-25
744003610-6
754003615-15
764003634
774003635
784003652
794003671
804003672
814004371-4
824004372-4
834004373-4
844004374-4
854004380
864004427
874004444-7
884004453-008
894004478-16
904004478-24
914004478-8
924004535-6
934004585
944004616-4
954004618-4
964004637
974004638
984004644
994005244
1004005502
1014005529
1024005579
1034005632
1044005645
1054005646
1064005647
1074005648
1084005659
1094005666
1104005667
1114005683
1124005684
1134005685
1144005686
1154005688
1164005689
1174005884
1184006021
1194006022
1204006046
1214100575
1224100577
1234100584
1244100585
1254100586
1264100587
1274100588
1284100590
1294100591
1304100592
1314100593
1324100596
1334100597
1344100598
1354100599
1364100889
1374100894
1384100895
1394100900-67
1402000499
1414001484
1424001485
1434001486
1444001487
1454001512
1464001519
1474001528-12
1484001528-24
1494001528-32
1504001529-3
1514001530-3
1524001531
1534001533-6
1544001534-6
1554001535-6
1564002778
1574002784
1584002785
1594002786
1604002787
1614002788
1624002789
1634002790
1644002791
1654002792
1664002793
1674002794
1684002795
1694002796
1704002806-1
1714002806-12
1724002806-24
1734002806-3
1744002806-6
1754002829
1764003782-6
1774003794-4
1784003864-4
1794003937
1804003938
1814003939
1824003946-004
1834004679
1844004758
1854004764-008
1864004782
1874004785
1884004799-6
1894004800-6
1904004855
1914004888-4
1924004902-16
1934004902-8
1944004921-10
1954004921-13
1964004921-15
1974004921-18
1984004921-20
1994004921-23
2004005698
2014005711
2024005712
2034005714
2044005763
2054005765
2064005769
2074005771
2084005774
2094005783
2104005792
2114005798
2124003112
2134003116-12
2144003116-24
2154003116-6
2164003118-008
2174003122
2184003395
2194003396
2204003397
2214003398
2224003399
2234003400
2244003431
2254003458
2264100264
2274100266
2284100950-67
2294001906-6
2304001922-4
2314001923-6
2324001924-6
2334001925-6
2344001926-6
2354001927-6
2364001928-6
2374001951-6
2384002978
2394002979-15
2404002979-25
2414002979-6
2424002993-6
2434003004
2444003062-6
2454003063-6
2464003064
2474003065
2484003066-6
2494003067-006
2504003069-6
2514003087
2524003088
2534003089
2544003090
2554003092
2564003093
2574100765
2584100766
2594100767
2604100768
2614100769
2624100770
2634100771
2644100772
2654100773
2664100774
2674100775
2684100776
2694100777
2704100778
2714100779
2724100780
2734100781
2744100782
2754100783
2764100784
2774100785
2784100786
2794100787
2804100788
2814100789
2824100790
2834100791
2844100792
2854100793
2864100794
2874100795
2884100796
2894100797
2904100798
2914100799
2924100803
2932003130-58
2942003130-80
2952003131-300
2962003131-360
2972003131-420
2982003131-480
2992003131-540
3002003131-600
3012003131-780
3022003132-040404
3032003136
3042003146
3052003161-12
3062003161-12.5
3072003161-17
3082003161-18
3092003161-20
3102003161-24
3112003161-30
3122003161-36
3132003161-45
3142003161-84
3152003162-24
3162003163-12
3172003163-15
3182003163-18
3192003163-21
3202003163-24
3212003163-3
3222003163-6
3232003163-9
3242003166-12
3252003166-25
3262003166-30
3272003166-35
3282003166-40
3292003166-45
3302003166-50
3312003166-65
3322003166-85
3334100821
3344100835
3354100839
3364100841
3374100842
3384100843
3394100854
3404100860
3414100867
3424100868
3434100870
3444100884
3454004040
3464004044-6
3474004046-6
3484004076
3494004105
3504004108
3514004110-12
3524004110-24
3534004110-30
3544004110-40
3554004110-50
3564004110-6
3574004110-65
3584004110-85
3594004112
3604004143-25
3614004167-006
3624004168
3634004205-3
3644004265
3654004266
3664004267
3674004268
3684004301-1
3694004302-1
3704004303-4
3714004314
3724004315
3734004316
3744004317
3754004319
3764004335-10
3774004336-10
3784001624
3794001677-24
3804001677-6
3814001714
3824001743
3834001746
3844001747
3854001748
3864001750
3874001751
3884001752
3894001772
3904001778
3914001779
3924001780
3934001783
3944001796-2
3954001807
3964004921-25
3974004921-5
3984004921-8
3994004989
4004004993-12
4014004993-6
4024004995
4034005014
4044005026
4054005027
4064005028
4074005029
4084005030
4094005031
4104005047
4114005057
4124005087
413200-0693-01
4144005089
4154005190
4164005209
4174005231
4184005233
4194002865-16
4204002865-22
4214002865-8
4224002867-13
4234002867-28
4244002867-39
4254002867-53
4264002867-63
4274002884
4284002909
4294002941
4304002968
4314100560
4324100563
4334100568
4344100569
4354100570
4364101129
4374101130
4384101200
4394101201
4404101204
4414101206
4424101207
4434101208
4444101209
4454101210
4464101211
4474101212
4484101213
4494101214
4504101215
4514101216
4524101217
4534101218
4544101220
4554101221
4564101222
4574101223
4584101224
4594101225
4604101235
4614101237
4624101239
4634101241
4644101243-01
4654101243-02
4664101243-03
4674101243-05
4684101243-06
4694101243-07
4704101243-08
4714101243-09
4724101243-10
4734101243-11
4744101243-12
4754101243-17
4764101243-18
4774101243-22
4784101243-24
4794101248
4804101249
4814101250
4824101252
4834101253
4844101254
4854101256
4864101257
4874101258
4884101259
4894101260
4904101261
4914101262
4924101263
4934101264
4944101265
4954101266
4964101267
4974101268
4984101269
4994101270
5004101271
5014101272
5024101273
5034101274
5044101275
5054101276
5064101277
5074101278
5084101279
5094101280
5104101281-300
5114101281-360
5124101281-420
5134101281-480
5144101281-540
5154101281-600
5164101281-780
5174101282
5184101283
5194101284
5204101285
5214101286
5224101287
5234101288
5244101289
5254101290
5264101291
5274101292
5284101293
5294101294
5304101295
5314101296
5324101297
5334101298
5344101299
5354101300
5364101301
5374101302
5384101303
5394101304
5404101306
5414101307
5424101313
5434101318
5444101319
5454101320
5464101326
5474101327
5484101328
5494101329
5504101331
5514101332
5524101333
5534101335
5544101336
5554101337
5564101340
5574101341
5584101342
5594101343
5604101344
5614101347
5624101348
5634101351
5644101352
5654101353
5664101354
5674101355
5684101356
5694101357
5704101358
5714101359
5724101361
5734101362
5744101363
5754101364
5764101365
5774101366
5784101367
5794101368
5804101369
5814101370
5824101411
5834101414
5844101415
5854101416
5864101417
5874101418
5884101419
5894101420
5904101421
5914101422
5924101423
5934101459
5944101460
5954101464
5964101465
5974101475
5984101476
5994101477
6004101478
6014101480
6024101481
6034101484
6044101486
6054101487
6064101488
6074101489
6084101491
6094101493
6104101494
6114101495
6124101496
6134101497
6144101503
6154101505
6164101506
6174101507
6184101508
6194101509
6204101510
6214101511
6224101512
6234101515
6244101516
6254101517
6264101517-04
6274101517-06
6284101517-08
6294101518
6304101519
6314101520
6324101521
6334101522
6344101523
6354101527
6364101528
6374101529
6384101530
6394101532
6404101533
6414101536
6424101537
6434101538
6444200100
6454200104
6464200105
6474200106
6484200107
6494200108
6504200109
6514200110
6524200111
6534200112
6544200113
6554200114
6564200115
6574200123
6584200124
6594200125
6604200132
6614200133
6624200134
6634200135
6644200136
6654200138
6664200139
6674200140
6684200141
6694200142
6704200143
6714200145
6724200150
6734200154
6744200155
6754200156
6764200157
6774200158
6784200159
6794200160
6804200161
6814200162
6824200163
6834200164
6844200165
6854200167
6864200169
6874200171
6884200172
6894200173
6904200174
6914200176
6924200177
6934200178
6944200179
6954200182
6964200190
6974200192
6984200301-05
699500-0033-02
700500-0446-01
Qualifying Ag Leader Parts
 

PFS12

New Member
Joined
Jan 28, 2014
Messages
33
Office Version
  1. 2016
Platform
  1. Windows
Here is my page for specific Model code;

Planting Parts Contest File.xlsx
A
1Model
266095
Qualifying CASE Parts
 

PFS12

New Member
Joined
Jan 28, 2014
Messages
33
Office Version
  1. 2016
Platform
  1. Windows
Here is how I bring the data in, with the last two columns where I want to total the dollars only if they qualify. This table needs to be fluid as I will be adding to it monthly.

Planting Parts Contest File.xlsx
ABCDEFGHIJKLMNOPQRST
1CusPart Number:BrSMNResponsible SalespersonOrder Number:Description:Invoice Open DateBilling Run DateAvg CostPrice:Ven#Price:Quantity Ordered:ModelClass:Ag LeaderCase
2SMITH061410056802JRIP214984 ROW CLUTCH IN2020050120200501540180AGLE017204AGSAGS
3SMITH061400162402JRIP21498CLUTCH, SURE ST20200501202005013840320AGLE01512016AGSAGS
4SMITH0617336853202JRIP21498HARNESS202005012020050178.498CASE07107.8166095660
5FESKO0014814093803NWOQ18246BRUSH,CIRC2020042120200501138.618CASE072161260080600
6FESKO001296783A103NWOQ18246BRUSH202004212020050187.1211.25CASE071351260080600
7FESKO0014753677603NWOQ18246DISC202004212020050124.4133.5CASE0733.5160080600
8FESKO0014745567903NWOQ18246COVER2020042320200501430.374.75CASE07747.51060080600
9FESKO0014753677603NWOQ18246DISC2020042320200501265.2133.5CASE07368.51160080600
10FESKO0014745567903NWOQ18246COVER202004242020050186.0674.75CASE07149.5260080600
11WILLO002700274-0003JRIP16449TUBE202005012020050111.814.75CASE0714.75166095660
12INT04MISC04NWOQ25492CUSTOMS202004082020050100GL601
13INT041403070004NWOQ25492Autom. Chopcont202004172020050113111748CLAA0217751AGSP
14INT041403433004NWOQ25492Open Interface20200417202005011191.442240CLAA0227001AGSP
15INT04CLAAS-JD-J04NWOQ25492CL-JD Bridge202004172020050125003300AGRA0136001AGSP
16INT04CL-R-M04NWOQ25492Claas Roof MNT202004172020050180112AGRA011151AGSP
17INT04D-JD-M04NWOQ25492JD Mount20200417202005014056AGRA01601AGSP
18INT044000724-1204NWOQ25691Cable - CAN Bus2020042820200501-225150AGLE01-320-2AGSPAGS
19INT04400576104NWOQ25691HARDWARE KIT -2020042820200501-135180AGLE01-180-1AGSAGS
20INT04410027804NWOQ25691DISPLAY BUNDLE2020042820200501-5171.256895AGLE01-6895-1AGSPAGS
21INT044100284-0204NWOQ25691unlock2020042820200501-33754500AGLE01-4500-1AGSPAGS
22INT04410047004NWOQ25691TILE PLOW KIT -2020042820200501-4087.55450AGLE01-5450-1AGSPAGS
23INT04410131604NWOQ25691DISPLAY CABLE K2020042820200501-228320AGLE01-345-1AGSPAGS
24INT04410132204NWOQ25691DISPLAY CABLE K2020042820200501-315420AGLE01-450-1AGSPAGS
25INT04ZTN55045-0404NWOQ25692KIT2020042820200501858.551115CASE071137.3166070660
26INT04ZTN55045-2104NWOQ25692PLATFORM KIT20200428202005011382.151795CASE071830.9166070660
27INT04ZTN101990-0004NWOQ25692PORT EXPANDER2020042820200501281.34395CASE07426.6166020660
28INT04ZTN96500-8504NWOQ25692SN: 561955004720200428202005014847.546995CASE077134.9166010660
29INT04ZTN125000-3004NWOQ25692ANTENNA20200428202005012363.93070CASE073131.4166070660
30INT04ZTN96551-0404NWOQ25692PASSCODE20200428202005013808.935300CASE075406166082660
31INT04ZTN96554-0204NWOQ25692PASSCODE, WM20200428202005011713.252225CASE072269.5166083660
32INT04ZTN96570-0204NWOQ25692SN:?2020042820200501639.1830CASE07846.6166010660
Data File
 

Similar threads

Watch MrExcel Video

Forum statistics

Threads
1,118,693
Messages
5,573,666
Members
412,547
Latest member
L9253
Top