search and match several values which are in different order.

**Neddsomehelp** · 10-14-2009, 05:14 AM

Hello,
Lets say these are the values in sheet 1, ROW 1:
B1: David
C1: 44673
D1:Mike
E1:88473
F1: Mika
G1 77463
H1:
I1:
J1:

And these are the Lets say these are the values in sheet 2 ROW 155:
B1: 77463
C1: 44673
D1:Mike
E1:
F1:
G1:
H1:88473
I1: David
J1 44673

As you can see, there are the same 9 values in both rows in both sheets ( (no value is important as well).
However, they are not located in the same row number (1 and 155) and the order in which they are written is different.

What I would like to do is to search all the rows in sheet 2,(columns B-J only) and if there is a match for all values in a specific row to a row in sheet 1 (again, order is not important), than the Value in column A from the specific row in sheet 1 will be copied to Column A in sheet 2 for that maching row.

Thank you so much for you help on this!!

**DonkeyOte** · 10-14-2009, 05:17 AM

Rather than people here recreating your file why not post a small representative sample with a sufficient amount of data to make testing meaningful.

Also - how many rows of data do you have in your "real" file ?

**Neddsomehelp** · 10-14-2009, 11:19 AM

Hi,
Sorry about that, I attached a file right now.
You can see that all the rows contain the same values but some are in different order, what I need is that the program will understand that these rows contain same values and than copy the US BRAND from column A(sheet 1) into US BRAND in sheet 2.
Sheet 1 contain 800 rows and sheet 2 contain over 15,000 rows...

The thing is, sheet 2 contain products that have the same active ingredients so there can be 10 rows that will get the same US BRAND name from Sheet 1.

Basically I am planning to complete as many US BRAND names in sheet2
(15,000 rows) by doing this search in several sheets similar to sheet 1 (these lists have US BRAND names in column A which I need to copy to column A in sheet 2).

**Neddsomehelp** · 10-14-2009, 02:35 PM

Anyone? I really have no idea where to get this info other than here.

**DonkeyOte** · 10-14-2009, 06:57 PM

I would say you're looking at VBA (UDF's) - are you open to that ?
I would probably look to create a UDF, utilised on both sheets to create a concatenated string of all values in each given row but sorted from small to large (inclusive of blanks & with say : delimiter) - this way you can easily conduct a search for a match from one sheet to the other.

Post back and I will review tomorrow if not resolved in the meantime.

**DonkeyOte** · 10-15-2009, 04:17 AM

Per my prior post the attached utilises a couple of UDF's to create a concatenated string which eases sheet comparison, you will of course need to ensure Macros are enabled in order for this approach to work.

The code specifically makes use of Chip Pearson's QSortInPlace Function which sorts the Ingredients into order (sorting Arrays in memory is non-trivial)

I hope it helps.

**Neddsomehelp** · 10-15-2009, 06:20 PM

DonkeyOte, thx alot for your help!
I have no understanding in VBA/UDF.. I opened the file you attached and I have the following comments
-If I change the name in column A, sheet 1 than the identical row in sheet 2 changes as well, thats good as it shows its dynamic.
-When I try to change one of the values in the row in sheet 1 than I get an error in sheet 2-which is perfectly ok as it is basically saying there is no match. The problem is when I put the original value back so both rows are identical but the name in column A does not return as it was and it stays as error.
Another question, does the search scan all the rows? or just the same row number in sheet 2? i.e. if there is a product in row 1, sheet 1 and it is identical to a product in sheet 2, row 155, will the value in A column be copied as well? or they have to be the same row number?

Appreciate your help on this very very much! you are a life saver

**DonkeyOte** · 10-16-2009, 04:22 AM

Originally Posted by Neddsomehelp

-When I try to change one of the values in the row in sheet 1 than I get an error in sheet 2-which is perfectly ok as it is basically saying there is no match. The problem is when I put the original value back so both rows are identical but the name in column A does not return as it was and it stays as error.

The above should not occur and in truth I can't replicate - are you sure you're putting back exactly the same value as before ?
(to test - change a value on Sheet1, look at Sheet2 for error, press CTRL + Z, go back to Sheet2 - still an error ?)

Originally Posted by Neddsomehelp

Another question, does the search scan all the rows? or just the same row number in sheet 2? i.e. if there is a product in row 1, sheet 1 and it is identical to a product in sheet 2, row 155, will the value in A column be copied as well? or they have to be the same row number?

To clarify...

The UDF (VBA) is used only to generate the concatenated strings you see in Column Q on both sheets.
The comparisons are conducted using a standard MATCH function as seen in Sheet2 Column A, eg:

=INDEX(Sheet1!A:A,MATCH(Q2,Sheet1!Q:Q,0))

So yes, all rows are reviewed and thus it does not matter where the match is located (should it exist). If there are multiple matches the above will return the Brand associated with the first match.

**Neddsomehelp** · 10-16-2009, 04:35 AM

Originally Posted by DonkeyOte

The above should not occur and in truth I can't replicate - are you sure you're putting back exactly the same value as before ?
(to test - change a value on Sheet1, look at Sheet2 for error, press CTRL + Z, go back to Sheet2 - still an error ?)

I deleted a value in sheet1 and than #NAME? appeared in column A, sheet 2.
As I said, thats how it should be as they do not match after the deletion.
Now, I pressed CTRL+Z, the value came back to it's initial stage BUT the value in column A, sheet 2 remains #NAME?
Any ideas?
Thx!

**DonkeyOte** · 10-16-2009, 04:49 AM

You shouldn't be getting #NAME? error at all.

Question(s)

Are you using the file I gave you ?

YES:

Are Macros enabled ?

NO:

Are Macros enabled ?

Is the code I provided in my file in your file also, stored in Modules ?

**Neddsomehelp** · 10-16-2009, 06:05 AM

mmm...
Sorry...........you are right, macros were on high security, once I change it to low than it works like magic!
I will work on the full list later tonight see how it works and report back.
THANK YOU VERY MUCH FOR YOUR HELP!!!

**DonkeyOte** · 10-16-2009, 07:11 AM

As a general rule I would advise you use Medium level security rather than Low - on Medium you will be prompted as and when you open the file as to whether or not you wish to Enable macros - on Low Macros are automatically enabled... this is fine if you know from whom each and every file you ever open is from (and you can trust) - far safer to give yourself the option of disabling code should you wish.

**Neddsomehelp** · 10-21-2009, 07:37 AM

It works thank you very much!

search and match several values which are in different order.

LinkBack

Thread Tools

Rate This Thread

Display

search and match several values which are in different order.

Re: search and match several values which are in different order.

Re: search and match several values which are in different order.

Re: search and match several values which are in different order.

Re: search and match several values which are in different order.

Re: search and match several values which are in different order.

Re: search and match several values which are in different order.

Re: search and match several values which are in different order.

Re: search and match several values which are in different order.

Re: search and match several values which are in different order.

Re: search and match several values which are in different order.

Re: search and match several values which are in different order.

Re: search and match several values which are in different order.

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions