Returning contents of row below if specific text is found in a columns' list

[dealerz]

Hello,

I am new here, and I have searched the forums to check whether this question has been dealt with but I can’t seem to find the exact answer.

I have a long list of customer IDs (ALLINFO) and other details (of which I want the email addresses). Unfortunately, the associated email addresses are mixed in with this long list of IDs.

All data is in column A of a calc sheet I have. I have managed to pull all the IDs to a column of a new sheet (customerinfo, col A), and I would like to now paste the email address associated to each of these IDs in to a new column (col B).

The list of info is such that when there is an email address, I know for sure that it belongs to the customer ID just above it. I assume I have to do a MATCH INDEX mix, but I don’t understand how I can ask it to pull out the cell content containing the email address which is just below it.

So in my new sheet I would like to have something like this:

Customer ID_A - If [Customer ID_A is in list on sheet ALLINFO, give me the row just below it, else nothing]
Customer ID_B - If [Customer ID_B is in list on sheet ALLINFO, give me the row just below it, else nothing]
and so on.

Do I have to do a SEARCH, MATCH, INDEX, LOOKUP ?? Am very confused as to what’s the best way forward.

Thanks for any help you can give.

[erAck]

Assuming your data is in A1:A9 and the search criterion (customer ID) is in C1, then this =OFFSET(A1;MATCH(C1;A1:A9;0);0) will return the cell just below the match.

To create a column of data use =IFERROR(IF(SEARCH("ID",A1,1)=1,OFFSET(A1,1,0),""),"") and pull/copy that formula down.

Btw, would be nice if additional information was not given as answer but by editing the original question instead.

[dealerz]

Thanks for replying! I tried this and it works (and I learnt a new trick!) but it’s a little different to what I wanted to do

Since the list is really long (4000 rows) and I don’t want to have to input an ID one at a time to ‘grab’ the email, I decided to filter out all the email addresses by looking for the ‘@’, copying the list to an editor, eliminating the spaces using the ‘lines operation’ tool, copying the list back to a new calc sheet in column B next to a new col A which holds the custIDs. I filtered the IDs out in the exact same way. Really clumsy I know, and my libreoffice program crashed doing it many times (it can’t seem to handle massive datasets?).

=IF(ISNUMBER(SEARCH(".@.",A1)),A1,"") -// A1 is the start of the first customer ID and will return blank since it doesn’t contain an email address. Do the same to extract the IDs to create a list of just IDs //

A1-A4000 contains a long list like this:

• List item

ID123456

email@123.com

ID45678

email@123.com

[some other info which can vary]

ID67890

email@456.com

[some other info maybe]

ID89012

email@567.com

ID23456

email@908.com

and I wanted to move the IDs to a new column, and then move the email addresses (just below) next to the IDs, without changing the order obviously. The result being a nice list with no empty rows of IDs and a nice list of email addresses.

I would have preferred to have done it much more efficiently but I was stumped on how to transfer from column A all the IDS without spaces into a fresh column, D, let’s say, and then transfer the associated email address in to col E, using a formula for all the cells down.