Search multiple criteria for item

[Nanaia]

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.

 

[Toadstool]

Hi Nanaia,

I think this is what you want...

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

	A	B

<tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Part[/TD]
[TD="align: center"]Dimensions[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]LFY[/TD]
[TD="align: center"]2x3x4[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]CTY[/TD]
[TD="align: center"]3x4x5[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]YQP[/TD]
[TD="align: center"]4x5x6[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]FYQ[/TD]
[TD="align: center"]5x6x7[/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]DKL[/TD]
[TD="align: center"]6x7x8[/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]KLF[/TD]
[TD="align: center"]7x8x9[/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]SHD[/TD]
[TD="align: center"]8x9x10[/TD]

[TD="align: center"]9[/TD]
[TD="align: center"]DHK[/TD]
[TD="align: center"]9x10x11[/TD]

[TD="align: center"]10[/TD]
[TD="align: center"]PAN[/TD]
[TD="align: center"]8x9x10[/TD]

[TD="align: center"]11[/TD]
[TD="align: center"]KFL[/TD]
[TD="align: center"]8x9x10[/TD]

[TD="align: center"]12[/TD]
[TD="align: center"]GDH[/TD]
[TD="align: center"]8x9x10[/TD]

[TD="align: center"]13[/TD]
[TD="align: center"]TYE[/TD]
[TD="align: center"]8x9x10[/TD]

[TD="align: center"]14[/TD]
[TD="align: center"]FLD[/TD]
[TD="align: center"]4x5x6[/TD]

[TD="align: center"]15[/TD]
[TD="align: center"]HKF[/TD]
[TD="align: center"]4x5x6[/TD]

</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.

	A	B	C	D	E	F	G	H

<tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Part[/TD]
[TD="align: center"]Dimensions[/TD]
[TD="align: center"]COUNTIF[/TD]
[TD="align: center"]Old Part 1[/TD]
[TD="align: center"]Old Part 2[/TD]
[TD="align: center"]Old Part 3[/TD]
[TD="align: center"]Old Part 4[/TD]
[TD="align: center"]Old Part 5[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]A22[/TD]
[TD="align: center"]2x3x4[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]LFY[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]B33[/TD]
[TD="align: center"]3x4x5[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]CTY[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]N55[/TD]
[TD="align: center"]4x5x6[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]YQP[/TD]
[TD="align: center"]FLD[/TD]
[TD="align: center"]HKF[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]M77[/TD]
[TD="align: center"]5x6x7[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]FYQ[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]M88[/TD]
[TD="align: center"]6x7x8[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]DKL[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]V33[/TD]
[TD="align: center"]7x8x9[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]KLF[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]X11[/TD]
[TD="align: center"]8x9x10[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]SHD[/TD]
[TD="align: center"]PAN[/TD]
[TD="align: center"]KFL[/TD]
[TD="align: center"]GDH[/TD]
[TD="align: center"]TYE[/TD]

[TD="align: center"]9[/TD]
[TD="align: center"]Z44[/TD]
[TD="align: center"]9x10x11[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]DHK[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]10[/TD]
[TD="align: center"]P66[/TD]
[TD="align: center"]1x2x3[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]11[/TD]
[TD="align: center"]K34[/TD]
[TD="align: center"]4x4x3[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

</tbody>

New​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2[/TH]
[TD="align: left"]=COUNTIF(Old!B:B,B2)[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D2[/TH]
[TD="align: left"]=IF(COLUMN()-3>$C2,"",INDEX(Old!$A:$A,AGGREGATE(15,6,ROW(Old!$B:$B)/(Old!$B:$B=New!$B2),COLUMN()-3)))[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E2[/TH]
[TD="align: left"]=IF(COLUMN()-3>$C2,"",INDEX(Old!$A:$A,AGGREGATE(15,6,ROW(Old!$B:$B)/(Old!$B:$B=New!$B2),COLUMN()-3)))[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F2[/TH]
[TD="align: left"]=IF(COLUMN()-3>$C2,"",INDEX(Old!$A:$A,AGGREGATE(15,6,ROW(Old!$B:$B)/(Old!$B:$B=New!$B2),COLUMN()-3)))[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G2[/TH]
[TD="align: left"]=IF(COLUMN()-3>$C2,"",INDEX(Old!$A:$A,AGGREGATE(15,6,ROW(Old!$B:$B)/(Old!$B:$B=New!$B2),COLUMN()-3)))[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H2[/TH]
[TD="align: left"]=IF(COLUMN()-3>$C2,"",INDEX(Old!$A:$A,AGGREGATE(15,6,ROW(Old!$B:$B)/(Old!$B:$B=New!$B2),COLUMN()-3)))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Nanaia]

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.

 

[Toadstool]

If each dimension is a column:

	A	B	C	D

<tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Part[/TD]
[TD="align: center"]Height[/TD]
[TD="align: center"]Width[/TD]
[TD="align: center"]Depth[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]LFY[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]4[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]CTY[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]5[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]YQP[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]6[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]FYQ[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]7[/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]DKL[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]8[/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]KLF[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]9[/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]SHD[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]10[/TD]

[TD="align: center"]9[/TD]
[TD="align: center"]DHK[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]11[/TD]

[TD="align: center"]10[/TD]
[TD="align: center"]PAN[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]10[/TD]

[TD="align: center"]11[/TD]
[TD="align: center"]KFL[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]10[/TD]

[TD="align: center"]12[/TD]
[TD="align: center"]GDH[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]10[/TD]

[TD="align: center"]13[/TD]
[TD="align: center"]TYE[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]10[/TD]

[TD="align: center"]14[/TD]
[TD="align: center"]FLD[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]6[/TD]

[TD="align: center"]15[/TD]
[TD="align: center"]HKF[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]6[/TD]

</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:

	A	B	C	D	E	F	G	H	I	J

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Part[/TD]
[TD="align: center"]Height[/TD]
[TD="align: center"]Width[/TD]
[TD="align: center"]Depth[/TD]
[TD="align: center"]COUNTIFS[/TD]
[TD="align: center"]Old Part 1[/TD]
[TD="align: center"]Old Part 2[/TD]
[TD="align: center"]Old Part 3[/TD]
[TD="align: center"]Old Part 4[/TD]
[TD="align: center"]Old Part 5[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]A22[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]LFY[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]B33[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]CTY[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]N55[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]YQP[/TD]
[TD="align: center"]FLD[/TD]
[TD="align: center"]HKF[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]M77[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]FYQ[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]M88[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]DKL[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]V33[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]KLF[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]X11[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]SHD[/TD]
[TD="align: center"]PAN[/TD]
[TD="align: center"]KFL[/TD]
[TD="align: center"]GDH[/TD]
[TD="align: center"]TYE[/TD]

[TD="align: center"]9[/TD]
[TD="align: center"]Z44[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]DHK[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]10[/TD]
[TD="align: center"]P66[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]11[/TD]
[TD="align: center"]K34[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

</tbody>

New-2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E2[/TH]
[TD="align: left"]=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)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F2
[/TH]
[TD="align: left"]=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)))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Nanaia]

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]

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]

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.

 

[Toadstool]

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]

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]

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.