Excel 2007 - creating a new list by matching data in separate columns 1

[jeffm777]

I have a spreadsheet I need some help with. I'm pretty sure I need to use the index and match functions but I can't figure it out.

I have a list of customer numbers in column A and in column G. If the customer numbers in these two columns match, I need to replace the customer number in column A with the value that's in column H. For example, cell A4 has a customer number of "3". Cell G3 has a customer number of "3" also so I need to take the value that's in cell H3 and move it to cell A4.

Thanks in advance for any help with this.

 

[SkipVought]

hi,

Well what happens when the value in column A does not equal the value in column G?

Assuming NOTHING then the simple formula in each row is...
[tt]
=if(a2=g2,h2,a2)
[/tt]
HOWEVER, this formula cannot be in column A!!! It must exist in any unused column. Once executed, then COPY the cells in THAT column and do an Edit > Paste Special -- VALUES in column A. There is no way to perform in one step.

Alternatively, use VBA (forum707)

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[jeffm777]

Column A will not always equal column G. I'm attaching the spreadsheet so you can see what I'm working on. I know the index and match formula will work - I just can't figure out how to do it.

 

[SkipVought]

So this is not a single table. Sorry, I misunderstood.
[tt]
B3: =MATCH(VALUE(A3),Original_Customer,0)
C3: =IF(ISNA(B3),A3,INDEX(NEW_Customer,B3,1))
[/tt]
Then copy column C and Paste Special into column A.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

here's a sample of the first 12 results in your list
[tt]
1
2119000
3119000
4119000
1019000
2129000
2229000
3189000
3339000
4449000
7499000
1162
[/tt]

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

BTW, a significant part of the problem is that you have TEXT as the lookup values and NUMBERS as the lookup range!

Why would you do that to yourself?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]