Need to search for cell value within cells in another column then return row value

Hi Guys

This one has been boggling my brain and I am hoping I can even describe what I am trying to do, to give anyone the opportunity to try and suggest a solution.

I have exported a lot of product data from our old system and we have a lot of products that are made up from products in the same list.

E.g.

Product 100, 101, 102, 103 and 104

These are each individual products, however product 100 is made up of 102 and 103, 104 is made up of 100 and 101 etc etc.

I have column A which is the product number

Column B is the UPC/GTIN of each product.

Colulmn C is a space separated list of any UPC/GTIN numbers that that product is used in.

Part Number| UPC Number | Used in assembly
Product 100 | 1234567890 | 1928374650 9876543210 7654920178
Product 102 | 1325465768 | 1928374650 9766654310 1888375655 1193756665

etc

I am hoping to search to see if the UPC number for each product is located in any cell in the Used in Assembly column. If it is the part number for the UPC number should be added to a column in the matched UPC number value found in the used in assembly column.

It would result in this

Part Number | UPC Number | Component 1 | Component 2 | Component 3 etc
Product 176 | 1928374650 | Product 100 | Product 102 |

Product 176 is made up of product 100 and 102, as its UPC number was found in the 'Used in assembly' column for each of the other two products.

I hope that made some sense.

Please let me know if I can clarify anything of explain further.

Thanks in advance.

Regards

Jonathan

I think it might be a bit clearer if you attached a sample Excel workbook - the yellow banner at the top of the screen explains how to do this.

Pete

I have uploaded an example file with our product range (it isn't sensitive).

Column A is product code
Column B is product UPC number
Column C is Other products (designated by their UPC) that this product is used in the assembly of.


I want to find the row for any matched UPC number from the used in assembly column, and add the matched product code to a column for that located product

Does that make sense?

Regards

See attachment

There are a few ways to do this with Excel 2016. Here is one...

Copy/paste columns A and B into Sheet2, enter headers into row 1, then use this formula in C2:

=IFERROR(INDEX(Sheet1!$A:$A,AGGREGATE(15,6,ROW(Sheet1!$C$1:$C$2560)/ISNUMBER(SEARCH($B7,Sheet1!$C$1:$C$2560)),COLUMNS($A:A))),"")

Drag the formula through K2 (or however far you need) and then down.

From here, you can copy/paste columns C:K as values to remove all formulas and make it faster.

Please see attached.

Hi

Thanks for the response. I just copied pasted the data from my file in full, as I have spent ages fiddling with this today, so didn't go through and curate the data again to remove all those anomaly rows.

Please ignore any rows with extremely short codes like that.

Every valid UPC number will be 12 characters starting with 79.

In the 'used in assembly' column, most valid UPC numbers are separated by a space, but some are not. However, as all will be 12 characters starting with 79 I thought that might be able to be worked around.

Regards

Holy sh*t!

That is literally exactly what I wanted!

I'm not even going to try and understand how that works, but it is greatly appreciated

Thanks so much!

Jonathan

Originally Posted by 63falcondude

There are a few ways to do this with Excel 2016. Here is one...

Copy/paste columns A and B into Sheet2, enter headers into row 1, then use this formula in C2:

=IFERROR(INDEX(Sheet1!$A:$A,AGGREGATE(15,6,ROW(Sheet1!$C$1:$C$2560)/ISNUMBER(SEARCH($B7,Sheet1!$C$1:$C$2560)),COLUMNS($A:A))),"")

Drag the formula through K2 (or however far you need) and then down.

From here, you can copy/paste columns C:K as values to remove all formulas and make it faster.

Please see attached.

Holy sh*t!

That is literally exactly what I wanted!

I'm not even going to try and understand how that works, but it is greatly appreciated

Thanks so much!

Jonathan

Happy to help!

=IFERROR(INDEX(Sheet1!$A:$A,AGGREGATE(15,6,ROW(Sheet1!$C$1:$C$2560)/ISNUMBER(SEARCH($B7,Sheet1!$C$1:$C$2560)),COLUMNS($A:A))),"")

This is essentially a lookup formula (like VLOOKUP or INDEX MATCH) that is modified to return multiple matches.

SEARCH($B7,Sheet1!$C$1:$C$2560) looks in column C for the value in cell B7. If it finds a match, it returns a number.
ISNUMBER(SEARCH(...)) then produces TRUE wherever SEARCH returns a number.
It does this for all cells in the column C range and produces an array of TRUEs and FALSEs {TRUE;TRUE;FALSE;...;FALSE}

ROW(Sheet1!$C$1:$C$2560) produces an array of row numbers {1;2;3;...;2560}
ROW(...)/ISNUMBER(SEARCH(...)) divides the array of row numbers by the array of TRUE and FALSE values coming from ISNUMBER(SEARCH(...))
What you are left with is an array of errors (where SEARCH was not found) and row numbers (where SEARCH was found).

The 15 in the AGGREGATE tells the function to take the kth smallest value in the array where k is given by COLUMNS($A:A)
The 6 in the AGGREGATE tells the function to ignore errors

COLUMNS($A:A) gives the number of columns in the range A:A (1) so AGGREGATE gives the 1st smallest value (row number) of the array.
When the formula is dragged to the right, it changes to COLUMNS($A:B), which is 2, so AGGREGATE gives the 2nd smallest value (row number) of the array.

Now that we have the row numbers, we can use INDEX to give us the value in column A of that row.

IFERROR just changes errors (where no matches were found) to blank.