You'll need to explain much more clearly how to identify the various pieces of information that you're looking for. For example, you say customer type might be in row 6 of column B. Are the other rows blank? If not, how would you be able to tell which row has the type?

And does the next customer's info start with a 1 in column A?

I don't know how to insert my table for a visual. If someone can provide direction on that, I will provide a visual of what the data looks like. However, to answer your questions, Column B will either have a zero or customer type as specified for the customer record in a row above it in Column A. Each new customer record is indicated with a number 1; all rows below it will have a zero until a new record is identified with a 1.

You can take a screenshot of your data table, or a portion of it, and upload it to a sharing site, like imgur.com. Then edit your question to include the supplied link.

(1) First of all, I guess you’re reading the customer information report directly into Excel (by copy and paste?) and then trying to reduce from 23 to 25 lines/rows per record (customer) to one row per record (customer). It would have been nice to state that explicitly. (2) Use words to describe your problem more clearly. OK, input records are variable-length, delimited by a 1 in Column A. Let’s put that aside for a moment, and pretend all input records are 25 lines/rows long. If the customer types were in cells B6, B31, B56, B81, B106, etc., this wouldn’t be too hard. … (Cont’d)

(Cont’d) …  But if the first customer type might be in B5 or B7, how do you tell? (3) Is it one field per column, or might there be another field in B18 (±1)? (4) Even if you believe that “providing a visual of what the data looks like” is essential to explaining your problem clearly, please don’t post images of text.  Post a textual representation of your data, as was done here and here; use the Format Text as Table … (Cont’d)

(Cont’d) …  or the Plain Text Tables generator site if you want.  You should probably post a couple of customer records (input and output), but, for the sake of illustration, pretend that the input records range between 8 and 10 rows.  Don’t paste so many columns that a row of your data doesn’t fit on a line of text.  (Of course don’t post any real, sensitive data.)

Thanks, G-Man. Unfortunately, my company blocks sharing sites and the customer report exported to excel.csv from our software is ugly. One customer record can be dispersed in up to four columns and up to 26 rows. I am not an excel guru so the terminology, while I understand what you're saying, is not lexicon with which I am familiar. But, yes, I need six key pieces of information to align in one record. I've used formulas to separate this information into one table but cannot figure out how to get it to display in one row. Below is the first column of my table:

+------------------------------------------------+ | Master (1,0) | +------------------------------------------------+ | =COUNTIF(A2239,"Customer Master File Report*") | +------------------------------------------------+

Here is the 2nd column of the table (all formulas in each column are copied down)

+-------------------------------------+ | Cust Type | +-------------------------------------+ | =IF(B2239="Customer Type:",C2239,0) | +-------------------------------------+

3rd column as follows...

+---------------------------+ | Site Ph | +---------------------------+ | =IF(A2239="Site",B2239,0) | +---------------------------+

4th column below...

+-------------------------------------------------+ | Sys Acct | +-------------------------------------------------+ | =IF(A2238="*** Systems ***",RIGHT(A2239,8),0) | +-------------------------------------------------+

5th column below...

+-----------------------------------+ | Column2 | +-----------------------------------+ | =COUNTIF(A2239,"Test Interval:*") | +-----------------------------------+

6th column below...

+-------------------------------+ | Test Interval | +-------------------------------+ | =IF(M2239=1,RIGHT(A2239,8),0) | +-------------------------------+

7th column below...

+-----------------------------------+ | Primary Contact | +-----------------------------------+ | =IF(C2239="Caller ID 1:",D2239,0) | +-----------------------------------+

8th and final column below...

+-----------------------------------+ | Secondary Contact | +-----------------------------------+ | =IF(C2239="Caller ID 2:",D2239,0) | +-----------------------------------+

Column 5 in the table is column M (cell reference in the column 6 formula). All other cell references in the formulas are where the exported data resides.

@StacyBullis All of that information in you comments should be in the question. Please edit it.

Some diagrams of your data would help us understand your question. Please edit your question to include some example (mock) data (before and after). See Format Text as a Table for a web utility that will help you to create a nice data table you can paste into your question.