Text Report to Spreadsheet

[cindee]

Hello! I have an excel spreadsheet that came from an
Accounting report that was laid out like this:

CUST0099
INV9880 09/06/02 1456.09
INV9767 09/01/02 547.09
Inv9990 09/08/02 235.60

I want to bring it into Excel in a true spreadsheet
format like this:

CUST0099 INV9890 09/08/02 235.60
CUST0099 INV9880 09/06/02 1456.09
CUST0099 INV9767 09/01/02 547.09

...but would have to manually copy the Customer Number to
each row (Invoice Line) and eyeball to stop copying when
I hit a "different" customer number. Is there a way to
do this in Excel? This report has over 45,000 lines to
doing it by hand is NOT an option.

Thanks in advance for any assistance.

Cindee

 

[Bernie Deitrick]

Cindee,

The easiest way is to import your data, then insert a new column A for
the Customer Numbers, and use a formula in column A to grab the
numbers.

How the formula will be written will depend on your actual data
layout. Does each record start in column A? Are there blank rows
between customers?

If yes to both of these, after inserting a new column A, use
=B1
in cell A1. In cell A2, use the formula

=IF(B2="","",IF(A1="",B2,A1))

If the answer was no to either of those questions, then you'll need a
different equation. For example, if there are no blank rows, instead
of using "" as the key, you will need to look at some other thing,
like the "CUST" part. If that is unique, then you could use, for
example

=IF(LEFT(B2,4)="Cust","",IF(A1="",B1,A1))

In any event, once you're done, you will need to delete the blank rows
and rows with CUST numbers in what is now column B. Sorting first,
then deleting the blanks and CUST numbers, then resorting will work
well.

HTH,
Bernie