Complex text extraction formula

[RhysPieces]

I receive monthly volume reports from my distributors with data arranged by
each of their customers. They arrive formatted in Excel 2003 or better that
has been generated from their ERP platform. The problem is they are not
easily sortable because of the layout: The first row for each customer
contains account information as a single text string including #, name,
address, etc in Col. A. Succeeding rows are by product but with separate
cells for item #, description, and quantity sold. The pattern then repeats
itself with each additional customer and the number of rows per customer
varies. I need to sort and subtotal by selected product criteria under each
customer because they aren’t that way as delivered.

To facilitate re-sorting, so I can categorize by product criteria, I want to
put the customer # in a new Col A. and the name in a new Col. B for each
product like in a flatfile DB or table. I imagine inserting a new Col. A and
B and then writing an extraction formula in each to parse the parts of the
text string I want and insert them at the beginning of each row that has
product data. It would need to be a conditional ‘=IF’ formula based on
whether the prior row has a text string greater than the six characters of
the customer number.
Got any ideas?

 

[Pete_UK]

It would help to visualise things if you could post a few examples of
your data.

Pete

 

[Ron Rosenfeld]

I receive monthly volume reports from my distributors with data arranged by
each of their customers. They arrive formatted in Excel 2003 or better that
has been generated from their ERP platform. The problem is they are not
easily sortable because of the layout: The first row for each customer
contains account information as a single text string including #, name,
address, etc in Col. A. Succeeding rows are by product but with separate
cells for item #, description, and quantity sold. The pattern then repeats
itself with each additional customer and the number of rows per customer
varies. I need to sort and subtotal by selected product criteria under each
customer because they aren’t that way as delivered.

To facilitate re-sorting, so I can categorize by product criteria, I want to
put the customer # in a new Col A. and the name in a new Col. B for each
product like in a flatfile DB or table. I imagine inserting a new Col. A and
B and then writing an extraction formula in each to parse the parts of the
text string I want and insert them at the beginning of each row that has
product data. It would need to be a conditional ‘=IF’ formula based on
whether the prior row has a text string greater than the six characters of
the customer number.
Got any ideas?

Perhaps if you supply some sample data, and how you would like the result to
appear, I could be more specific.

But I'd probably just cycle through your existing report and generate a new DB,
with the appropriate number of columns.

The algorithm could be something like this:

i=1
for each row in report
If it is account_info then
save acct#; save name
else (on a different worksheet)
colA_Row(i).value = acct#
colB_Row(i).value = name
colC_Row(i).value = row
i= i+1
end if
next row
--ron

 

[Shane Devenshire]

Hi,

Show us what the incoming data looks like and what you want it to end up
looking like. You can use dummy data but maintain the exact structure of
your data.

 

[Spiky]

I receive monthly volume reports from my distributors with data arranged by
each of their customers. They arrive formatted in Excel 2003 or better that
has been generated from their ERP platform. The problem is they are not
easily sortable because of the layout:  The first row for each customer
contains account information as a single text string including #, name,
address, etc in Col. A. Succeeding rows are by product but with separate
cells for item #, description, and quantity sold. The pattern then repeats
itself with each additional customer and the number of rows per customer
varies. I need to sort and subtotal by selected product criteria under each
customer because they aren’t that way as delivered.

To facilitate re-sorting, so I can categorize by product criteria, I wantto
put the customer # in a new Col A. and the name in a new Col. B for each
product like in a flatfile DB or table. I imagine inserting a new Col. A and
B and then writing an extraction formula in each to parse the parts of the
text string I want and insert them at the beginning of each row that has
product data. It would need to be a conditional ‘=IF’ formula basedon
whether the prior row has a text string greater than the six characters of
the customer number.
Got any ideas?

Assuming you meant that the ID # is always exactly 6 digits, try
this...

E1 or whatever is the first empty column:
=IF(B1="",A1,INDIRECT("E"&ROW()-1))

Then, F1:
=LEFT(E1,6)

Then, G1:
=RIGHT(E1,LEN(E1)-6)

Copy all those down as far as necessary. Then you can insert an extra
column or 2 before A. Next, Copy & Paste Values from F:G into A:B.
Then delete all the excess columns. Then Sort on column D since you
should have many rows without data in D. Delete the rows you don't
need, they should be at the top or bottom, depending on your sort.