Split cell into two and move to another row and columns

[maacmaac]

I have a report that I need to reformat where part of the information is moved from rows to columns.

The report is broken up into "sections" as follows: Each “section” is a series of multiple rows and is broken down as follows:

ROW 1: Contains data (in a single cell) about a Sales rep, which includes (1) rep number & (2) rep name
NEXT ROW(s): Contains data information about an invoice(s), which includes date, invoice number, client name, trans ID, etc. The invoice data can be one row up to as many as 500 rows
LAST ROW: Contains the Rep Subtotal

I need to spit out a report that contains the invoice data only (the middle part of the “section”). I don't want "ROW 1" or "LAST ROW" of each section in the output. For each invoice row, I need to include the rep number and the rep name for each invoice. As noted, the rep number and name is always listed in the row preceding the invoice data. The format is always a 6-digit code followed by the name. So I need to split the data into two pieces.

Hope this makes since. I have attached a sample for clarification, which includes the raw data and a sample of the desired output. Thank you in advance for any comments.

[jrdnoland]

I don't have time to program this right now, but I'll give you some pseudocode:

I have to make some assumptions that the format will remain consistent, that the first entry will be in Range("A8").

Get the last row in column A in which data appears subtract 3 from this to account for the last 3 rows in column A

Starting in Range("A8") [Cells(8,10)] check if cell value is
not a date and/or not "Rep SubTotal"

This gives you the Rep ID and Rep Name
remove the 6 digit Rep ID and put in a variable
remove the hyphen
trim the Rep Name and put in variable

Go to the next cell (should be a date)
if IsDate = True then copy the values in the row to the output sheet (keep track of the last row or find it each time)

Go to the next cell ......
if is "Rep SubTotal" goto next cell (should be another Rep ID and Rep Name)
remove the 6 digit Rep ID and put in a variable
remove the hyphen
trim the Rep Name and put in variable

Go to the next cell .... (should be a date again)
if IsDate = True then copy values




This give you an idea of how to approach the coding.

[maacmaac]

Thanks for the response. As per your comments, the data is consistant. I took your comments and tried putting in a macro but really struggling with the code. Any further assistance you can give me is much appreciated. Thanks again.

[jaslake]

Hi maacmaac
The attached workbook and code appear to do as you described. The code is as follows:

Please Login or Register  to view this content.

Let me know if you have any issues.
John

[maacmaac]

John, Thanks for the code

I was able to get the code working but only after I created a worksheet 4. I wasn't sure why you are using the worksheet 4 in the code. If can explain what it is used for, I would appreciate it. If possible, I would like to have only two worksheets in the file as to not cause any confusion to the user. Thanks

Please Login or Register  to view this content.

[jaslake]

Hi maacmaac
I have no idea where sheet4 came into the picture. Change that line of code from

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Only two sheets are required-RawData and Output. The other two sheets are simply copies of your original worksheets.
If you have issues, let me know.
John

[jaslake]

Hi maacmaac
You mentioned wanting to have only two sheets; we could simplify it further by having the procedure create the Output sheet. You would only need the RawData worksheet.
Let me know your thoughts.
John

[maacmaac]

If I could get the code to create the output sheet, that would be great. I did run into one issue with this part of the code:

Please Login or Register  to view this content.

I keep getting an "overflow" error when it hits the line highlighted in blue. I have ~50,000 rows of data and I think that is causing the problem but not sure how to fix it.

Thanks again for your comments.

[jaslake]

Hi maacmaac
Try

Please Login or Register  to view this content.

instead of

Please Login or Register  to view this content.

Let me know if that helps. Actually, I've already done this

If I could get the code to create the output sheet, that would be great.

I'll post the code later but let me know about the "Overflow" issue.
John

[jaslake]

This link http://www.mrexcel.com/forum/showthread.php?t=67603 confirms that

Please Login or Register  to view this content.

should work
John

[maacmaac]

that solved the "overflow" issue. Code is working fine. Thanks for your assistance.

[jaslake]

Hi maacmaac
Glan it worked out. I'll post the code that creates the output sheet later this evening.
John

[jaslake]

Hi maacmaac
This code will create the "Output" sheet and "do your thing" on "Raw Data". Let me know how it works for you.
John

Please Login or Register  to view this content.