Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex text extraction formula
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 arent 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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex text extraction formula
It would help to visualise things if you could post a few examples of
your data. Pete On Dec 31, 12:34*am, RhysPieces wrote: 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 arent 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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex text extraction formula
On Tue, 30 Dec 2008 16:34:01 -0800, RhysPieces
wrote: 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 arent 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex text extraction formula
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: 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 arent 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? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex text extraction formula
On Dec 30, 6:34*pm, RhysPieces
wrote: 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 arent 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with Text import and data extraction | Excel Discussion (Misc queries) | |||
Text string extraction | Excel Worksheet Functions | |||
Inconsistant character spaces extraction formula | Excel Discussion (Misc queries) | |||
Extraction Formula or Array? | Excel Discussion (Misc queries) | |||
Complicated extraction of text | Excel Discussion (Misc queries) |