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
 

PFS12

New Member
Joined
Jan 28, 2014
Messages
33
Office Version
  1. 2016
Platform
  1. Windows
I think I have some formatting issues because the part numbers contain - or letters in some instances. Any help with this that you can provide would be great. Sorry for the hassle
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Similar threads

Watch MrExcel Video

Forum statistics

Threads
1,118,074
Messages
5,570,041
Members
412,308
Latest member
cornelb
Top