Ask Your Question
0

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

asked 2017-05-29 14:21:35 +0200

dealerz gravatar image

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.

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
0

answered 2017-05-29 15:22:04 +0200

erAck gravatar image

updated 2017-05-30 15:20:12 +0200

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.

edit flag offensive delete link more
0

answered 2017-05-30 12:28:16 +0200

dealerz gravatar image

updated 2017-05-30 14:16:49 +0200

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.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2017-05-29 14:21:35 +0200

Seen: 386 times

Last updated: May 30 '17