quick and easy excel query.

DogsBody · 29 Sep 2011 at 01:33

In cells A1 to A1000 I have a list of data, I want to find the last populated cell in colum B and display the corresponding number from column A.

I can copy the data from A to another cell if it make this easier.
Ideally this would be done with a formula but if it isn't possible without a macro then i will settle for that.

Thanks
James

Sin_Chase · 29 Sep 2011 at 02:51

So you want to search for the last Row populated in Column B then return the value from the same Row in Column A?

Where do you want that value displayed?

Is 'last populated' cell "A populated cell in Column B with highest numbered row" or "The last cell to be edited in Column B"

This is very easy with VBA, doubt you can get this functionality out of a Formula alone though.

DogsBody · 29 Sep 2011 at 10:12

Currently there are no blank rows in the middle of the data but it could happen in the future. Best way to do this would be to select the first populated cell from the bottom rather than last populated cell from the top I think.

Also how difficult is it to get Excel to store customer details in another spreadsheet and only add them if they dont already exist (based on matching name AND postcode, in case I get 2 names the same)

Thanks
James

Participant · 29 Sep 2011 at 12:06

If column A is numeric:

If column A is text:

Courtesy http://www.mrexcel.com/forum/showthread.php?t=15062.

DogsBody · 29 Sep 2011 at 14:24

Robbie you have your formulas the wrong way round but the link you provided gave me what I was looking for.

Thanks.
James