• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Identify matching single values from 2 arrays that both have multiple values

C

cau83

Guest
Matching 2 lists is covered, however I have not found a solution for the type of matching I need to do.


I have 2 lists-- each one has many values. List 1 is setup like this:

Account Detail

A&P A&P TCA

xxx xxxxx


List 2 is like this:

Store Char1 Char2 Char3 etc.

33 Big Y Shoe A&P TCA

46 G TCA Fair Popsicle


List 2 has ~25 columns. Each store does not have 25 characteristics-- it varies. The characteristics are not in any particular order. I want to create a column in List 2 that tells me if any of the characteristics values in that row match any of the details from list 1. In other words, match one array against another array and look for a match of any individual value.


In the example I gave above, I'd get output telling me that store 33 does match a detail from List 1, but Store 46 does not.


Does that make sense? Any ideas?

Thanks,

Chris
 
Hi Fred,

You can access the file here:

https://www.sugarsync.com/pf/D6379953_624_075116989


I have bolded the value that I am looking to match in list 2. The gold solution is that excel tells me Y or N a value matches (the first column I created) and what it is (2nd column). An acceptable solution would be just telling me if it matches and then I can manually find it. My actual list has 1000s of stores and this will narrow it down tremendously.


Thanks!
 
Note: I have figured out a crude way to match the list i showed by searching for the string "$2 MM". However, I need to do this same kind of match with other strings that need to matched exactly, but I was trying to attach the simplest example I had.
 
Hi cau83,


Check out following file...and give feedback.

http://www.4shared.com/office/5dfK_RAV/FV-MVP-key-chandoo-11.html


Question: What if, if there are two entries that come up with certain criteria for your mentioned example?


Regards,

FASEEH
 
Faseeh,

That is a creative way to help match up these values, thank you. However, I need a solution that is more dynamic-- it must match the entire string, because I have another list that does not have this CITY $2 MM layout. Instead, it has other values that do not follow any specific naming convention.


While List 2 (the one arrayed in rows) has duplicates, list 1 will not. Each row in list 2 will only have one (or zero) matches in list 1. So, there should not be a situation in which "there are two entries that come up with certain criteria". In the example I gave, there is only one of these geography tags in each row, and List 1 has a list of the unique geography tags.
 
Hi ,


In your worksheet , in cell B2 have the following formula :


=SUMPRODUCT((((store_char!E2:S2)=mkt_Range))*(COLUMN(store_char!E2:S2)*ISTEXT((mkt_Range))))


entered as an array formula ( with CTRL SHIFT ENTER ) ; mkt_Range is the range B2:B9 on the "mkt" tab.


Copy it down in column B.


Format all these cells in column B with a Custom Format : "Y";"N";"N"


In cell C2 , have the following formula :


=IFERROR(INDEX(E2:AA2,B2-COLUMN(E2)+1),"")


Copy it down , in column C.


Please note that you need to change the text BUFFALO/ROCHESTER in your "store-char" tab , to whatever is there on your "mkt" tab viz. BUFFALO-ROCHESTER.


Narayan
 
Hi ,


Sorry about the shortened range in the formula in my earlier post ; the actual range would be :


=SUMPRODUCT(((store_char!E2:AA2)=mkt_Range)*(COLUMN(store_char!E2:AA2)*ISTEXT(mkt_Range)))


Narayan
 
Narayank,

This worked great. Thanks a lot. I did not pick up this project again until yesterday but it gave me exactly what I needed-- I ended up changing some data so that 99.9% of things only had one match and this then ID'd them.
 
You must log in or register to reply here.
Back
Top