how do I move a cell based on a match of another cell in excel?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have two lists of customer sales info and I want to line them up on a given
row based on customer number (columns A and E). I know about the vlookup
function, but I want to move, not copy the data. That way I can identify
those is the second group (column E) that did not have a match in the first
group (column A).
 
Insert a new column A, and in cell A2, enter the formula (and then copy down to match your list)

=IF(ISERROR(MATCH(B2,F:F,FALSE)),1000,ROW())

Then in a blank column beyond your "column E" list (which is now column F), enter this formula into
the cell in row 2 (and copy down to match your list):

=IF(ISERROR(MATCH(F2,B:B,FALSE)),1000,MATCH(F2,B:B,FALSE))


Then copy / paste values for column A, and do the same for the second column of formulas. Then sort
your first list (and associated data) by column A Ascending, and the same for the second list / data
/ column of values from the formula above.

HTH,
Bernie
MS Excel MVP
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

how to show whether 3 cells within a row match 3 cells in another 3
match and sort two different tables 2
Match a group of cells with another group of cells 1
sum column based on single cell match? 1
Basing math on duplicate entries in a formula 1
Add a new row in Named Table (Table1) and add data from txtboxes into last empty cells based on header named of the Table1 0
Excel VBA to move cursor from 1 cell to another, once original cell is filled with data. 3
Reference the same cell based on tab name 1

Back
Top