CODEWITHSUNDEEP
excel
Wolfgang
Wolfgang

Reputation: 575

To to arrange non sequencial IDs in order

I have three excels. All containing same IDs and phone numbers may be differ, Id is alphanumeric and we have total 10,000 records, but IDs are not present in same sequence across all excel sheets.
For example:
If one ID is present in row 2000 in sheet 1, same ID is present at 3200 row and in sheet 3 it is present in row 5200. 

 Sheet 1                           Sheet2                      
   ID      | Contact Number|     |       ID       | Contact Number |
MP-XX-098  |  89652395     |     |    KJ-OP-98    |    3323241     |                         
KJ-OP-98   |  3323241      |     |    MP-XX-098   |    89652395    |                   
OP-MK-09   |  9632211      |     |    UI-32-09    |    3234521     | 
UI-32-09   |  3234521      |     |    OP-MK-09    |    9632211     |

I need to create single excel to find which system is having different records.

for example

Sheet                           
       ID      | Contact Number(1) |  contact number(2) |Contact Number(3) |
    MP-XX-098  |  89652395         |       89652395     |    89652395     |                         
    KJ-OP-98   |  3323241          |          3323241   |     3323241     |                   
    OP-MK-09   |  9632211          |      9632211       |   9632211       | 
    UI-32-09   |  3234521          |        3234521     |    3234521      |

Please note I already tried sort A-Z but it is not working.

Upvotes: 1

Views: 62

Answers (2)

Ken
Ken

Reputation: 4887

Vlookup: use vlookup to find the corresponding value in each sheet:

   ID      | Contact Number(1)                          |  contact number(2)                  |Contact Number(3) |
MP-XX-098  |  =vlookup(a2,sheet1!$A$2:$b$100,  2, FALSE)|  =vlookup(a2, sheet2!$A$2:$b$100, 2,FALSE)|    89652395     |

vlookup will search a range for a value (in this case ID) and return the nth column of the row where the value is found.

In this case the range to search is sheet1!$A$2:$b$100, the value to fins in in the first column: a2 and we need the value in the 2nd column: 2

Upvotes: 2

Roger Sinasohn
Roger Sinasohn

Reputation: 483

To clarify Ken's answer a bit...

What you'll likely want to do is copy the IDs to a new sheet in, say, column A. then in columns B, C, and D, you'll put formulas such as Ken posted.

Note that Ken's formulas have a typo -- the search value comes first and then the search range. See this page at office.com for more info. So they really should be:

=vlookup($a2, sheet1!$A$2:$b$10001, 2, FALSE)

The first parm for vlookup is the cell address of the value you want to look up. That's the one in the current sheet, over in column A. If your first one is on row 2, then you'd use $A2 in your vlookup formulas. You want the $ before the A so that it always looks in column A, but not in front of the 2 because you want it to use the value on the same row as the formula. (So you can do this in cell B2 and copy it to C2 and D2, then use Fill Down to copy the formulas to all the rows.)

The second parm for vlookup is the search range -- that will be the range containing the ID and Contact Number in each of the other sheets. (e.g., if your ID is in column A and Contact Number in column B and they start on row 2 and there are 10k records, you'd use sheet1!$A$2:$B:10001 where "sheet1" is the name of the first worksheet.)

The third parm is the column in your search range from which you want to copy your value -- in this case, it's the contact number in the second column of the search range. (Note that this the column of the search range, not of the worksheet.)

The last parm, FALSE, just says to use an exact match, rather than find the closest.

Then, if you want to flag those rows where there is a discrepancy (so you can just scan them to find the problems), use something like this in Column E:

=IF(OR($B5 <> $C5,$B5 <> $D5), "***", "")

This will put three asterisks (***) in column E for each row where one of the contact numbers differs from one of the others.

Hope this helps!

Upvotes: 1

Related Questions