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

Add different values in a column to another list of values

anafilipe

New Member
Hi,

I would like your help. I usually need to match specific values from one column to a list in another. Usually I do it manually by copy pasting, but would love to know how to do it with a formula.

A simple example is:
upload_2016-5-20_15-32-36.png

What formula can I use to do this?

Thanks
 

Attachments

Chihiro

Excel Ninja
Something like below?
=CHOOSE(COUNTIF($A$1:A2,A2),$D$2,$D$3,$D$4)

Assuming each set of Text1~Text6 will have distinct match. You will need to extend formula as needed.
4th set will need something like...
=CHOOSE(COUNTIF($A$1:A2,A2),$D$2,$D$3,$D$4,$D$5)

Or you can set it up with index# for list of matches and use something like...
=INDEX($F$2:$F$8,MATCH(COUNTIF($A$1:A2,A2),$E$2:$E$8,0))

See attached.
 

Attachments

Chihiro

Excel Ninja
Oh wait. You can shorten second version to something like. No need for index#.
=INDEX($F$2:$F$8,COUNTIF($A$1:$A2,A2))

To make it dynamic use named range.
upload_2016-5-20_11-2-10.png

Formula becomes...
=INDEX(lstMatch,COUNTIF($A$1:$A2,A2))
 

Attachments

anafilipe

New Member
Hi Chihiro,

Thank you very much for your quick answer.

I tried your formulas and really like that -
=CHOOSE(COUNTIF($A$1:A2,A2),$D$2,$D$3,$D$4) and =INDEX(lstMatch,COUNTIF($A$1:$A2,A2))

I'm sure I'm going to use them a lot.

Now, what do you recommend when we have the 2 lists as unique values and we want to have the 2 columns as mentioned before? The formulas you mention work when the one of the lists have the right rows already. So, when I have the 2 unique lists and want to match them and create the equivalent columns, what is possible to do? The results don't need to have a specific order as long as the combination of list 1 to all elements of list 2 (or other way around) are there.

Thanks!

upload_2016-5-20_18-18-10.png
 

Attachments

Chihiro

Excel Ninja
In that case, there's 2 ways of doing it.

1. Formula method. See link for details.
http://www.mrexcel.com/forum/excel-questions/654871-how-generate-all-possible-combinations-two-lists-without-macro.html

2. Use MS Query to produce Cartesian Product Join of 2 list

My preferred method is 2. There's few steps involved here.

1. Create 2 sheets in the workbook. Each containing 1 of the list and covert the list to table. Lets call each sheet List1 and List2. Save the file.

2. Go to Data tab in ribbon tool and find "Get External Data"->"From Other Sources"->"From Microsoft Query"

3. Choose "Excel Files" and click ok.

4. Navigate and select the file saved in step 1 and hit ok.

5. You should see something like below. If not, click on Options and check "System Tables".
upload_2016-5-20_13-42-31.png

6. Expand List1$ & List2$ and move over columns (list to match 1, list to match 2) over to right. Then hit "Cancel".

7. Popup window will appear, click "Yes".

8. You should see table populated. Click on "File" and "Return Data to Microsoft Excel".

9. Choose where you want to return the data. See attached for end result.
 

Attachments

Top