CODEWITHSUNDEEP
vbaexcelspreadsheet
David Tunnell
David Tunnell

Reputation: 7542

Order 2 columns by matching to another column

I have 4 columns in an excel file: enter image description here

The two on the right have about 9000 rows, the 2 on the left have about 14000.
Column A and B are connected. Also, C & D are connected.

The THER_CLASS column is the 1st 6 numbers of the key column.

How would I order the left two columns by if the 1st 6 of the key matches THER_CLASS?
I want each row to take the A column find which row the 1st 6 # of GPI are equal and then move C and D to the row that A is on.

I could do this by building a console app in c# but I feel like there is a way to to it much easier in excel.

Upvotes: 1

Views: 66

Answers (1)

Blackhawk
Blackhawk

Reputation: 6140

You're basically just looking up the "Category" of each "Item". As Flephal points out, you can do this with VLookup.

  1. Add a column E with the header "CLASS" and a column F with the header "CLASS_DESC".
  2. For column E, starting with cell E2 and filling downwards, use the formula "=VLOOKUP(Left(C2, 6), A:B, 1, False)"
  3. For column F, starting with cell F2 and filling downwards, use the formula "=VLOOKUP(Left(C2, 6), A:B, 2, False)"

Really, it would make much more sense if you didn't have the two tables mashed up next to one another and instead had each one on a different sheet. For example, move A & B to another sheet and call it "CLASSES".

Upvotes: 1

Related Questions