All geek questions in one place

How can I sort one data set to match another data set in Excel?

I have a list of data that is not in alphabetical or numerical order. I want to sort the second list of the same date to match the first list. I cannot reorder the data. My goal is to insert additional data from the second dataset back into the first dataset.

**DATA SET A** **DATA SET B** 22350 ____ BH160 100 22355 ____ 22350 125 BH160 ____ BH190 200 BH190 ____ 22355 150

I would like to get a numeric value from column 2 of DATA SET B to display in a new column of DATA SET A. For example, I want 125 to appear in row 1, column 2 of DATA SET 1.

My list contains about 200 unique numbers, and I have to update it every week.

Can someone help me? Maybe with a macro that I could use every week?

+6
source share
3 answers

You can also use INDEX MATCH , which is more "powerful" than vlookup. This will give you exactly what you are looking for:

enter image description here

+9
source

You can use VLOOKUP .

Assuming they are in columns A and B in Sheet1 and Sheet2 each, 22350 is in cell A2 of Sheet1, you can use:

 =VLOOKUP(A2, Sheet2!A:B, 2, 0)

This will return you #N/A if there are no matches. Drag / Fill / Copy & Paste the formula at the bottom of the table and it should do it.

+4
source

You can also simply link both cells and have the = Cell formula in each column, for example =Sheet2!A2 in Sheet 1 A2 and =Sheet2!B2 in Sheet 1 B2, and drag it down and then sort these two columns as Do you want to.

  • If they are not sorted the way you want, put the order you want to sort in another column and sort all three columns by it.
  • If you drag it further and get zeros, you can edit the formula = Cellular formula to show " IF , nothing. = (If (cell =" "," ", cell)
  • Cutting, pasting, deleting, and pasting strings is tedious. #REF! .

It would be better if your unique items also changed, then everything you would do would be sorted and done.

0
source

Source: https://habr.com/ru/post/952147/

More articles:


All Articles