Seeking formula that populates cells with data if rule met

[Jiquada]

Hi,

I can't upload my s/sheet due to work security protocols but I'm desperate to get this problem I have sorted so thought I'd try my luck at explaining the problem I'm trying to solve.

In column A I have a list of account numbers, some of which are duplicates. In column B I have a word or blanks.

For example; in cells A2:A4 I have the same account number 127689. Cells B2:B3 are blank but cell B4 I has the word Internet. In cells A5:A7 I have same account number 10064425. Cell B5 is blank, B6 has the word Mobile, B7 is blank.

I need a formula in column C that will populate all the correspondinding blank cells in column B with the word where the account number in column A is the same.

So the formula in cells C2:C4 would populate these cells with the word Internet because cells A2:A4 have the same account number. Cells C5:C7 would be populated with the word Mobile because A5:A7 has the same account numbers.

Any help greatly appreciated.

 

[taigovinda]

Are the data sorted in any way? In your example the account numbers are sorted...

I suggest that you sort by column A, then by column B so that B2 says Internet, B5 says Mobile, etc. (I think this would be sort descending).

Then, in column C you can simply use this formula:

=vlookup(A1,$A$1:$B$9999,2,0)

Because of the sorting, you know that for each account number the top entry will contain the value you want in column B; vlookup(,,,0) - as above - will always return the *first* exact match.

If for some reason you don't want to sort the data, then I expect the solution will be more complicated.

Hope that helps - if not post back!

Tai