how to compare two lists with duplicates

Question

suppose the data are as follows

a

1. a
2. b
3. c
4. d
5. a
6. e
7. a
8. so on

 and the next data is

b

1. a
2. b
3. c
4. d
5. a
6. a
7. so on

now while i vlookup(second,first table,1,false),..........when the excel tests for 1a (form b) it shows corresponding  form 1a(in table a).  ...but while testing 5a and 6a (table b) then also it returns the value from 1a only (in table a)...

so the question is how to test the second and third 'a' (in table b) with the corresponding second and third 'a'(in table a) and not just the first 'a'.

Answer 1

Hi,

Your question is not clear but this article may help

http://office.microsoft.com/en-gb/excel-help/how-to-look-up-a-value-in-a-list-and-return-multiple-corresponding-values-HA001226038.aspx

Answer 2

a

i have changed 4d to 4a from my previous example

1. a   1S.no
2. b   2
3. c   3
4. a  4
5. a   5
6. e   6
7. a   7

            b

1. a 1S.no
2. b 2
3. c 3
4. d 4
5. a 5
6. a 6

what i wnt to tell was ,,,, when i vlookup(second, first table,2,false)... i.e i want to check from which s.no, has the data been extracted.... when it looks for 'a' in table a, it tells that all are from s.no1.... for a in sno. 5  and s.no 6 in table b also, it tells it is from sno1...

what i want is

when looking a in s.no5 intable b it should tell it is from s.no 4(table a)

and when looking s.no.6(table ),,,, it should tell it is from s.no5

in other words... it should first a and first a... second a and second a and third a and third a  and not...... first second and third a (table b) with the first a in table a only.

am i clear now?

Answer 3

Hi shailendrauprety,

It would be better if you just print screen e desired result other than describing it with words. Having you understood (hoping) well, here are some steps to perform your desired result. It’s a little late but this might help you in the future. Below is the starting data according to your last edit.

What you need is a key that represents the order of appearance of a value. Add a column and write down the formula as you see it in the below picture. Pay attention to the $ sign before the number.

Drag the created formula in the following cells and you will see a result similar to the picture below. Getting “a” as an example you see that the first “a” gets the number 1, the second “a” gets the number 2 and so on.

Next you need to make the results of the KEY column related to the corresponding values. We do this by just concatenating the KEY values with the LABEL corresponding values. Edit the formula in cell C4 and copy it in the following cells to replace the old formula with the new edited one.

You will see a result similar to the picture below.

Do the same steps as above to create KEY column values for the next table. After doing so you will get the result as below.

At this stage you can use the new KEY columns to join the two tables. This can be done by using the VLOOKUP function. In order for the VLOOKUP function to work the KEY column must be on the left side of the targeting table, also the VLOOKUP function gets only the first value it finds and does not give you other value combinations. (This depends on repetition of your data between tables).

Many times I deal with table matching, table joining, lists comparing… so for that purpose I wrote an Add In for Excel to help automate the process. Follow the steps below on how to join data between two tables.

First you need to create the tables. Select the cells from the first table (Including headers) then go to INSERT tab and in the Tables group click the Table button. In the Create table dialog box check “My data has headers” the n click OK.

Just not to get confused it is best to name the new table. Click on any cell in the created table then go to DESIGN tab and in the Properties group change the table name to TableA.

Select the cells from the second table (Including headers) then go to INSERT tab and in the Tables group click the Table button. In the Create table dialog box check “My data has headers” the n click OK.

Just not to get confused it is best to name the new table. Click on any cell in the created table then go to DESIGN tab and in the Properties group change the table name to TableB.

To join tables go to GEGPRIFTI tab and in the Database group click the Join tables button. In the Join tables dialog box:

• Select TableB from the Left table list.
• Select TableA from the Right table list.
• By default in left table output columns the first column is marked as Key column. You need to change it. Right click on KEY column and select “Set as key column” from the meny.
• Right click on the KEY column from the right table output columns and select “Set as key column” from the menu.
• For this specific case you need to match all values from the TableB with any values from TableA. The key column that join two tables are named KEY in both tables. Click the second button to select LEFT OUTER JOIN join type.

Click OK to see the results.

In the results worksheet you see that the first value of “a” in TableA is matched with the first value of a in TableB and so on for the rest.

If you want a different result just play with the different join types prior generating the results.

By default key matching is not case sensitive. If you want it to be case sensitive then go to Database group settings and change it.

More info about the tool here