Results 1 to 2 of 2

Thread: "if" this text found, keep this row and the next -- delete others. how to do this?

  1. #1

    "if" this text found, keep this row and the next -- delete others. how to do this?



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

  2. #2
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    794
    Articles
    0
    Excel Version
    2010
    Hello Tim
    I would lean towards using a VBA solution for this. You haven't mentioned any actual columns, so it would probably be a good idea if you could post an example report, so that your helper can work out:

    1. Is a report on one worlksheet or several
    2. Will the client name appear in the same column on each row, and will it be the only text in the column it appears in ? Where do you specify the client name to keep?
    3. As 2 above but read date/time in place of client name.
    4. Is it just the "one particular client" that utilises two rows, or some others/all clients? Does Row 2 also have the client name?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •