"if" this text found, keep this row and the next -- delete others. how to do this?
Two questions:
1.) I have 4 reports that i have to search thru and keep only the rows that contain certain text (the client name) AND the row after that, because i need to know how long a digital billboard ad ran on one particular client (row 1) before switching to the next client (row 2). And that client will appear on thousands of individual rows throughout the report.
Because each of the reports is approximately 60,000 rows in length, and the client in question appears on maybe every 3, to 5, to 12 rows, i'd like to delete the unnecessary rows. But i need to keep the next row after the particular client appears, because that next row down tells me how long my client's ad ran (which is what i'm looking for -- a sum of all the seconds they ran for the entire 60,000 row report).
so, i'd like to say something like, "If ACME is found in a row, keep that row and the next, but delete all others" which will allow me to see only the rows i need instead of having to look at all 60,000 rows.
2.) One column of the report has both the date and the time in it. for example:
That cell in row 1 reads: 5/29/15 12:08:06 p.m.
That cell in row 2 reads: 5/29/15 12:08:14 p.m.
This means the ad in row 1 ran for 08 seconds before switching over to the client in row 2. If i could delete the date from that column and leave only the time, then i could write a formula that would say something like: sum=(2a-1a) and that would give me the # of seconds the ad in Row 1 ran (which is what i'm after), then i could just total that column after running that formula throughout the whole many thousand row report.
Suggestions and/or directions for each of the two questions above?
I did this all manually for the first report just by conditional formatting / color highlighting the client name so at least i could find them quickly ... but i have 3 other reports to do. it took hours for the first one, so i'm hoping there's a formulaic way to make the remaining 3 reports "do what i need them to do" which is way less labor intensive.
Thank you much.
Tim
Two questions:
1.) I have 4 reports that i have to search thru and keep only the rows that contain certain text (the client name) AND the row after that, because i need to know how long a digital billboard ad ran on one particular client (row 1) before switching to the next client (row 2). And that client will appear on thousands of individual rows throughout the report.
Because each of the reports is approximately 60,000 rows in length, and the client in question appears on maybe every 3, to 5, to 12 rows, i'd like to delete the unnecessary rows. But i need to keep the next row after the particular client appears, because that next row down tells me how long my client's ad ran (which is what i'm looking for -- a sum of all the seconds they ran for the entire 60,000 row report).
so, i'd like to say something like, "If ACME is found in a row, keep that row and the next, but delete all others" which will allow me to see only the rows i need instead of having to look at all 60,000 rows.
2.) One column of the report has both the date and the time in it. for example:
That cell in row 1 reads: 5/29/15 12:08:06 p.m.
That cell in row 2 reads: 5/29/15 12:08:14 p.m.
This means the ad in row 1 ran for 08 seconds before switching over to the client in row 2. If i could delete the date from that column and leave only the time, then i could write a formula that would say something like: sum=(2a-1a) and that would give me the # of seconds the ad in Row 1 ran (which is what i'm after), then i could just total that column after running that formula throughout the whole many thousand row report.
Suggestions and/or directions for each of the two questions above?
I did this all manually for the first report just by conditional formatting / color highlighting the client name so at least i could find them quickly ... but i have 3 other reports to do. it took hours for the first one, so i'm hoping there's a formulaic way to make the remaining 3 reports "do what i need them to do" which is way less labor intensive.
Thank you much.
Tim