|
|
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?
|
|
It would help to visualise things if you could post a few examples of
your data.
Pete
On Dec 31, 12:34 am, RhysPieces <RhysPie...[ at ]discussions.microsoft.com>
wrote:
[Quoted Text] > 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?
|
|
On Tue, 30 Dec 2008 16:34:01 -0800, RhysPieces
<RhysPieces[ at ]discussions.microsoft.com> wrote:
[Quoted Text] >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
|
|
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.
--
If this helps, please click the Yes button
Cheers,
Shane Devenshire
"RhysPieces" wrote:
[Quoted Text] > 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?
>
|
|
On Dec 30, 6:34 pm, RhysPieces <RhysPie...[ at ]discussions.microsoft.com>
wrote:
[Quoted Text] > 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?
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.
|
|
|