Bit of Excel Coding

[schnabs]

Hello,
I have two sheets. Sheet 1 has all of my information on it, save one column of data, which is on sheet two. I need to check to see if the name on sheet1 matches the name on sheet 2. If it does, I want to populate the cell in sheet1 with the data from 2, for each customer. My problem is, on sheet1, the customers have spaces inbw each name, so while customer 1 is in a1, customer 2 might be in a4 or a5. On sheet 2, they are in numerical order.
Thanks in advance for all your help.

 

[SkipVought]

Hi,

Please post examples that demonstrate your dilema. Be sure to post CORREPSONDING samples from both lists.

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[schnabs]

Sure thing.
Here is what my data looks like.
Sheet1
Column A Column I
Row1 Bill
Row2
Row4
Row4
Row5 Tom
Sheet 2

Column A Column I
Row 1 Bill Cleared
Row 2 Tom Pending
Row 3 Zack Cleared
Row 4 Bob Cleared
Row 5 Erica Pending

I need a macro/statement that would check if it says Bill on both sheet 1 and sheet 2, it would populate column I on sheet 1 with the data from column I on sheet 2. My problem is, the spaces between the names. Row 2 sheet 2 column A equals Tom, but Tom is on sheet 1, row 5 column A.

 

[steph22]

Do you need this to be a macro? Or are you able to use a vlookup in column I on Sheet 1?

=IF(COUNTBLANK(A1)>0,"",VLOOKUP(A1,Sheet2!$A$1:$I$5,9,FALSE))

gives you the following:

Column A Column I
Row1 Bill Cleared
Row2
Row4
Row4
Row5 Tom Pending

 

[SkipVought]

"My problem is, on sheet1, the customers have spaces inbw each name"

This is a similar problem as your had in one of your other threads, where you had to delete ALL the rows of customers with ZERO balances.

I gave you an example of a METHOD. The same METHOD can be applied in this instance, looking at the NAME rather than the AMOUNT.

You ought to be able to apply this method to this new case. One caveat, might be that if you have a Total Robert then you are going to have to find the Robert part, so your formula might look something like this...
[tt]
B1: =if(a2="",b1,if(left(a2,5)="Total",right(a2,len(a2)-6),a2))
[/tt]


Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]