Search multiple criteria for item

Nanaia

Board Regular
Joined
Jan 11, 2018
Messages
220
Office Version
365
Platform
Windows, MacOS
I'm not sure how to word it, but here goes...
I have two spreadsheets (1 old & 1 new) I need to compare specific criteria. I need to search the old CSV of parts and compare to the new CSV of parts and tell me if we have old parts that match the dimensions of the new parts. The CSV headers in each are identical, but the part numbers have changed. I'm trying to find an easy way to have Excel tell me what the old part number is as a cross reference for the new part number. e.g. If new part 101 is 2x2x1 and old part numbers 609 and 845 are 2x2x1 then Excel list 609 and 845 as the result.

I used COUNTIFS to have it tell me how many match. I'm looking for a way to have it tell me what the associated old part numbers for those matches are. I know there's a way, but I haven't figured out how.
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
548
Office Version
2016
Platform
Windows
Hi Nanaia,

I think this is what you want...

Here's the "Old" tab with parts and dimensions:

AB
1PartDimensions
2LFY2x3x4
3CTY3x4x5
4YQP4x5x6
5FYQ5x6x7
6DKL6x7x8
7KLF7x8x9
8SHD8x9x10
9DHK9x10x11
10PAN8x9x10
11KFL8x9x10
12GDH8x9x10
13TYE8x9x10
14FLD4x5x6
15HKF4x5x6

<tbody>
</tbody>
Old

Here's the "New" tab with the new parts and dimensions and the COUNTIF.
The "Old Part n" columns will need to be copied to the right for as many as the MAX COUNTIF.
The formulae in C to H will need to be copied down every data row.
You don't say how many rows of data you have so I'm checking all 1 million+ but you can make the formulae run faster if you include range limits.


ABCDEFGH
1PartDimensionsCOUNTIFOld Part 1Old Part 2Old Part 3Old Part 4Old Part 5
2A222x3x41LFY
3B333x4x51CTY
4N554x5x63YQPFLDHKF
5M775x6x71FYQ
6M886x7x81DKL
7V337x8x91KLF
8X118x9x105SHDPANKFLGDHTYE
9Z449x10x111DHK
10P661x2x30
11K344x4x30

<tbody>
</tbody>
New

Worksheet Formulas
CellFormula
C2=COUNTIF(Old!B:B,B2)
D2=IF(COLUMN()-3>$C2,"",INDEX(Old!$A:$A,AGGREGATE(15,6,ROW(Old!$B:$B)/(Old!$B:$B=New!$B2),COLUMN()-3)))
E2=IF(COLUMN()-3>$C2,"",INDEX(Old!$A:$A,AGGREGATE(15,6,ROW(Old!$B:$B)/(Old!$B:$B=New!$B2),COLUMN()-3)))
F2=IF(COLUMN()-3>$C2,"",INDEX(Old!$A:$A,AGGREGATE(15,6,ROW(Old!$B:$B)/(Old!$B:$B=New!$B2),COLUMN()-3)))
G2=IF(COLUMN()-3>$C2,"",INDEX(Old!$A:$A,AGGREGATE(15,6,ROW(Old!$B:$B)/(Old!$B:$B=New!$B2),COLUMN()-3)))
H2=IF(COLUMN()-3>$C2,"",INDEX(Old!$A:$A,AGGREGATE(15,6,ROW(Old!$B:$B)/(Old!$B:$B=New!$B2),COLUMN()-3)))

<tbody>
</tbody>

<tbody>
</tbody>
 

Nanaia

Board Regular
Joined
Jan 11, 2018
Messages
220
Office Version
365
Platform
Windows, MacOS
Awesome! Thank you for pointing me in the right direction. If the dimensions each have their own columns (length, width, thickness), how would that change the formula?

Customer revised their order and detailing decided to create new parts instead of using existing parts and only creating new ones for the parts that haven't already been made. I'm trying to find a way to find the old in hundreds of lines of data so production can just find the box the old parts are in and doesn't have to make new ones. It's a waste of time and material otherwise.

Thanks again.
 
Last edited:

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
548
Office Version
2016
Platform
Windows
If each dimension is a column:

ABCD
1PartHeightWidthDepth
2LFY234
3CTY345
4YQP456
5FYQ567
6DKL678
7KLF789
8SHD8910
9DHK91011
10PAN8910
11KFL8910
12GDH8910
13TYE8910
14FLD456
15HKF456

<tbody>
</tbody>
Old-2

Then the COUNTIFS needs to check all three dimensions and the formula needs to change so the second part of AGGREGATE is TRUE for all three dimensions:

ABCDEFGHIJ
1PartHeightWidthDepthCOUNTIFSOld Part 1Old Part 2Old Part 3Old Part 4Old Part 5
2A222341LFY
3B333451CTY
4N554563YQPFLDHKF
5M775671FYQ
6M886781DKL
7V337891KLF
8X1189105SHDPANKFLGDHTYE
9Z44910111DHK
10P661230
11K344430

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
New-2

Worksheet Formulas
CellFormula
E2=COUNTIFS('Old-2'!B:B,'New-2'!B:B,'Old-2'!C:C,'New-2'!C:C,'Old-2'!D:D,'New-2'!D:D)
F2
=IF(COLUMN()-5>$E2,"",INDEX('Old-2'!$A:$A,AGGREGATE(15,6,ROW('Old-2'!$A:$A)/('Old-2'!$B:$B=$B2)*('Old-2'!$C:$C=$C2)*('Old-2'!$D:$D=$D2),COLUMN()-5)))

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 

Nanaia

Board Regular
Joined
Jan 11, 2018
Messages
220
Office Version
365
Platform
Windows, MacOS
Thank you for the suggestions. Using the formula shown in F2 provides me the part number in the same row number that the formula is located. So if the formula shown above is used in New-2 cell F14, the results supplied are from Old-2 cell (designated column) row 14. The old CSV and new CSV have the same headers, however the item in the old row 14 does not correspond in any with with the new row 14. It's a rolling inventory so the rows could be any part number. It's an interesting formula though. I've never worked with AGGREGATE before.
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
548
Office Version
2016
Platform
Windows
Nanaia,

I'm not sure I understand "the item in the old row 14 does not correspond in any with with the new row 14" as the new row 14 has no data in my example.

I understood you wanted to take the new parts list and identify any from the old parts list with the same dimensions. That's what the formula does.

The Old row 14 is for part FLD with dimensions of 4x5x6 (and two other parts on the Old list share those dimensions, YQP and HKF). On the New list in row 4 there is a part N55 with dimensions 4x5x6 so my formula looks for those dimensions anywhere in the Old list are gives the matching old part numbers in F4, G4 and H4 (YQP, FLD, HKF).

Toadstool
 

Nanaia

Board Regular
Joined
Jan 11, 2018
Messages
220
Office Version
365
Platform
Windows, MacOS
You are correctly understanding what I want to do. What I'm saying is that the formula doesn't give the correct results. Don't take old row 14 and new row 14 as a reference to your example. When put in my spreadsheet (correcting to have it look at my tab names in place of yours) it is doing the same thing for every row. Replace 14 from my reply with 3 if you want to use your example. If the formula is in New-2 cell row 2 the results are Old-2 cell (designated column) row 2 If the formula is in New-2 cell row 3 the results are Old-2 cell (designated column) row 3. If the formula is in New-2 cell row 4 the results are Old-2 cell (designated column) row 4. It does not appear to be searching for the part numbers correctly.
 
Last edited:

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
548
Office Version
2016
Platform
Windows
I cannot diagnose without seeing what you have (maybe a missing $?) so if you can't provide your spreadsheet then please supply the headings, old and new tab names and the first AGGREGATE formula in your sheet. I will then recreate a sample sheet.
 

Nanaia

Board Regular
Joined
Jan 11, 2018
Messages
220
Office Version
365
Platform
Windows, MacOS
I'm unable to upload the file. Here is a link to a shared file: Shared Excel file

The old data is on the tab with "Old" in the name, the new data is on the tab with "New" in the name. I am comparing the new face length, face width, type, edge, etc. columns against the old ones to see which of the old part (mark) numbers would work to be used for the new part (mark) numbers. I'm doing my comparing and calculations on the "comparison" tab. Right now I'm simply filling in the old mark numbers as I go through the laborious task of using filters on the "old" tab looking for each combination found on the "new". It's a pain in the bum. As I mentioned before, I know there's a way to get Excel to hunt for me but I can't get it to work properly.
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
548
Office Version
2016
Platform
Windows
OLD_CSV_Multiple column K "Direction" is 10 characters, e.g. "V ", so will never match the 1 character "V" from the NEW_CSV_Multiple list. The easiest one-off fix is to highlight column K and do a find & replace of all spaces with a null (and it should tell you it's replaced 5,832 occurrences). Now your COMPARISON column F will show matches in 90 rows for a potential 419 matches against the OLD file.

I'd simplify the comparison by concatenating into a work area.
On OLD_CSV_Multiple add in column BJ =Q2&"x"&R2&"x"&K2&"x"&L2&"x"&J2
On COMPARISON column H add =A2&"x"&B2&"x"&D2&"x"&E2&"x"&C2
Now you can just look for matches on those two columns.

You only have two IDs (151788 and 151789) so listing them is no use. Instead I'll show the row numbers of matches.

This formula takes time to run so I would only copy into column I where your QTY in column F is greater than zero
=IF(COLUMN()-8>$F2,"",AGGREGATE(15,6,ROW(OLD_CSV_Multiple!$BJ:$BJ)/(OLD_CSV_Multiple!$BJ:$BJ=$H2),COLUMN()-8))
You can then copy right the number of columns shown by QTY so you get each row number on OLD_CSV_Multiple where there's a match.
 

Watch MrExcel Video

Forum statistics

Threads
1,089,886
Messages
5,410,999
Members
403,336
Latest member
amreeves87

This Week's Hot Topics

Top