Is there any formula that I could use to be able to sort the below
information into a format that I could create a pivot table on?

Invoice #: 12345 Invoice Date: 1/16/1950 A/P Code: ABC
Due Date: 1/16/1950 Total Payable: $100.00 Reference: Freight:
Account #: 1234 Description: Name Reference Amount: $100.00
Account #: 4321 Description: Name Reference 2 Amount: $100.00

Any help would be greatly appreciated

The right way to do this task is to have a unique column for each field. A
macro can be writen that looks at each line and automatically moves the data
to a unique column in a new worksheet. It is hard to tell from the posting
exactly how to write the code because I can't tell which data is in which
columns.

the new worksheet would look something like this on row 1 (the commas
showing a different column). Will create the header row with the columns and
put the data in the corrrect column.

Invoice #, Invoice Date, A/P Code, Due Date, Total Payable,
Reference Freight, Account #, Description, Name Reference, Name
Referrence 2,

"Mark" wrote:

Is there any formula that I could use to be able to sort the below
information into a format that I could create a pivot table on?

Invoice #: 12345 Invoice Date: 1/16/1950 A/P Code: ABC
Due Date: 1/16/1950 Total Payable: $100.00 Reference: Freight:
Account #: 1234 Description: Name Reference Amount: $100.00
Account #: 4321 Description: Name Reference 2 Amount: $100.00

Any help would be greatly appreciated

The format looks like this

A B C D
E F
Invoice #: 12345 Invoice Date: 1/16/1950 A/P Code: ABC
Due Date: 1/16/1950 Total Payable: $20.00 Reference: Freight:
Account #: 1234 Description: Name Reference Amount: 10.00
Account #: 4321 Description: Name Reference 2 Amount: 10.00

The detail table I would like to be able to produce is Invoice, Invoice
data, AP Code, Due Date, Account #, Description, Amount. The biggest problem
I am running into is being able to match the Account # on A3 to the
information on A1:F2. Another problem that could arise is there could
possible be up to 12 different account numbers. Do you have any coding
examples for this? I am not the greatest at writing VBA.

"Joel" wrote:

The right way to do this task is to have a unique column for each field. A
macro can be writen that looks at each line and automatically moves the data
to a unique column in a new worksheet. It is hard to tell from the posting
exactly how to write the code because I can't tell which data is in which
columns.

the new worksheet would look something like this on row 1 (the commas
showing a different column). Will create the header row with the columns and
put the data in the corrrect column.

Invoice #, Invoice Date, A/P Code, Due Date, Total Payable,
Reference Freight, Account #, Description, Name Reference, Name
Referrence 2,

"Mark" wrote:

Is there any formula that I could use to be able to sort the below
information into a format that I could create a pivot table on?

Invoice #: 12345 Invoice Date: 1/16/1950 A/P Code: ABC
Due Date: 1/16/1950 Total Payable: $100.00 Reference: Freight:
Account #: 1234 Description: Name Reference Amount: $100.00
Account #: 4321 Description: Name Reference 2 Amount: $100.00

Any help would be greatly appreciated

Sub splitdata()


Sh2LastCol = 1
With Sheets("Sheet1")
.Rows(1).Insert 'add blank row so rows in sheet 1 & 2 are the same
RowCount = 2
Do While .Range("A" & RowCount) < ""
ColCount = 1
Do While .Cells(RowCount, ColCount) < ""
Category = Trim(.Cells(RowCount, ColCount))
Data = Trim(.Cells(RowCount, ColCount + 1))
With Sheets("Sheet2")
Set c = .Rows(1).Find(what:=Category, _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
.Cells(1, Sh2LastCol) = Category
.Cells(RowCount, Sh2LastCol) = Data
Sh2LastCol = Sh2LastCol + 1
Else
.Cells(RowCount, c.Column) = Data
End If
End With
ColCount = ColCount + 2
Loop
RowCount = RowCount + 1
Loop
End With
End Sub

"Mark" wrote:

The format looks like this

A B C D
E F
Invoice #: 12345 Invoice Date: 1/16/1950 A/P Code: ABC
Due Date: 1/16/1950 Total Payable: $20.00 Reference: Freight:
Account #: 1234 Description: Name Reference Amount: 10.00
Account #: 4321 Description: Name Reference 2 Amount: 10.00

The detail table I would like to be able to produce is Invoice, Invoice
data, AP Code, Due Date, Account #, Description, Amount. The biggest problem
I am running into is being able to match the Account # on A3 to the
information on A1:F2. Another problem that could arise is there could
possible be up to 12 different account numbers. Do you have any coding
examples for this? I am not the greatest at writing VBA.

"Joel" wrote:

The right way to do this task is to have a unique column for each field. A
macro can be writen that looks at each line and automatically moves the data
to a unique column in a new worksheet. It is hard to tell from the posting
exactly how to write the code because I can't tell which data is in which
columns.

the new worksheet would look something like this on row 1 (the commas
showing a different column). Will create the header row with the columns and
put the data in the corrrect column.

Invoice #, Invoice Date, A/P Code, Due Date, Total Payable,
Reference Freight, Account #, Description, Name Reference, Name
Referrence 2,

"Mark" wrote:

Is there any formula that I could use to be able to sort the below
information into a format that I could create a pivot table on?

Invoice #: 12345 Invoice Date: 1/16/1950 A/P Code: ABC
Due Date: 1/16/1950 Total Payable: $100.00 Reference: Freight:
Account #: 1234 Description: Name Reference Amount: $100.00
Account #: 4321 Description: Name Reference 2 Amount: $100.00

Any help would be greatly appreciated

This seems to be somewhat working, is there anyway I could send an example
file and show you problem I run into?

"Joel" wrote:

Sub splitdata()


Sh2LastCol = 1
With Sheets("Sheet1")
.Rows(1).Insert 'add blank row so rows in sheet 1 & 2 are the same
RowCount = 2
Do While .Range("A" & RowCount) < ""
ColCount = 1
Do While .Cells(RowCount, ColCount) < ""
Category = Trim(.Cells(RowCount, ColCount))
Data = Trim(.Cells(RowCount, ColCount + 1))
With Sheets("Sheet2")
Set c = .Rows(1).Find(what:=Category, _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
.Cells(1, Sh2LastCol) = Category
.Cells(RowCount, Sh2LastCol) = Data
Sh2LastCol = Sh2LastCol + 1
Else
.Cells(RowCount, c.Column) = Data
End If
End With
ColCount = ColCount + 2
Loop
RowCount = RowCount + 1
Loop
End With
End Sub

"Mark" wrote:

The format looks like this

A B C D
E F
Invoice #: 12345 Invoice Date: 1/16/1950 A/P Code: ABC
Due Date: 1/16/1950 Total Payable: $20.00 Reference: Freight:
Account #: 1234 Description: Name Reference Amount: 10.00
Account #: 4321 Description: Name Reference 2 Amount: 10.00

The detail table I would like to be able to produce is Invoice, Invoice
data, AP Code, Due Date, Account #, Description, Amount. The biggest problem
I am running into is being able to match the Account # on A3 to the
information on A1:F2. Another problem that could arise is there could
possible be up to 12 different account numbers. Do you have any coding
examples for this? I am not the greatest at writing VBA.

"Joel" wrote:

The right way to do this task is to have a unique column for each field. A
macro can be writen that looks at each line and automatically moves the data
to a unique column in a new worksheet. It is hard to tell from the posting
exactly how to write the code because I can't tell which data is in which
columns.

the new worksheet would look something like this on row 1 (the commas
showing a different column). Will create the header row with the columns and
put the data in the corrrect column.

Invoice #, Invoice Date, A/P Code, Due Date, Total Payable,
Reference Freight, Account #, Description, Name Reference, Name
Referrence 2,

"Mark" wrote:

Is there any formula that I could use to be able to sort the below
information into a format that I could create a pivot table on?

Invoice #: 12345 Invoice Date: 1/16/1950 A/P Code: ABC
Due Date: 1/16/1950 Total Payable: $100.00 Reference: Freight:
Account #: 1234 Description: Name Reference Amount: $100.00
Account #: 4321 Description: Name Reference 2 Amount: $100.00

Any help would be greatly appreciated

Yes
joel dot warburg at itt dot com

I think the problem is Reference and Freight have no data following these
items. I may need to check if a colon is atr the end of the data item to
determine if there is data or no data.


"Mark" wrote:

This seems to be somewhat working, is there anyway I could send an example
file and show you problem I run into?

"Joel" wrote:

Sub splitdata()


Sh2LastCol = 1
With Sheets("Sheet1")
.Rows(1).Insert 'add blank row so rows in sheet 1 & 2 are the same
RowCount = 2
Do While .Range("A" & RowCount) < ""
ColCount = 1
Do While .Cells(RowCount, ColCount) < ""
Category = Trim(.Cells(RowCount, ColCount))
Data = Trim(.Cells(RowCount, ColCount + 1))
With Sheets("Sheet2")
Set c = .Rows(1).Find(what:=Category, _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
.Cells(1, Sh2LastCol) = Category
.Cells(RowCount, Sh2LastCol) = Data
Sh2LastCol = Sh2LastCol + 1
Else
.Cells(RowCount, c.Column) = Data
End If
End With
ColCount = ColCount + 2
Loop
RowCount = RowCount + 1
Loop
End With
End Sub

"Mark" wrote:

The format looks like this

A B C D
E F
Invoice #: 12345 Invoice Date: 1/16/1950 A/P Code: ABC
Due Date: 1/16/1950 Total Payable: $20.00 Reference: Freight:
Account #: 1234 Description: Name Reference Amount: 10.00
Account #: 4321 Description: Name Reference 2 Amount: 10.00

The detail table I would like to be able to produce is Invoice, Invoice
data, AP Code, Due Date, Account #, Description, Amount. The biggest problem
I am running into is being able to match the Account # on A3 to the
information on A1:F2. Another problem that could arise is there could
possible be up to 12 different account numbers. Do you have any coding
examples for this? I am not the greatest at writing VBA.

"Joel" wrote:

The right way to do this task is to have a unique column for each field. A
macro can be writen that looks at each line and automatically moves the data
to a unique column in a new worksheet. It is hard to tell from the posting
exactly how to write the code because I can't tell which data is in which
columns.

the new worksheet would look something like this on row 1 (the commas
showing a different column). Will create the header row with the columns and
put the data in the corrrect column.

Invoice #, Invoice Date, A/P Code, Due Date, Total Payable,
Reference Freight, Account #, Description, Name Reference, Name
Referrence 2,

"Mark" wrote:

Is there any formula that I could use to be able to sort the below
information into a format that I could create a pivot table on?

Invoice #: 12345 Invoice Date: 1/16/1950 A/P Code: ABC
Due Date: 1/16/1950 Total Payable: $100.00 Reference: Freight:
Account #: 1234 Description: Name Reference Amount: $100.00
Account #: 4321 Description: Name Reference 2 Amount: $100.00

Any help would be greatly appreciated

I sent it over thank you for taking a looking at it.

"Joel" wrote:

Yes
joel dot warburg at itt dot com

I think the problem is Reference and Freight have no data following these
items. I may need to check if a colon is atr the end of the data item to
determine if there is data or no data.


"Mark" wrote:

This seems to be somewhat working, is there anyway I could send an example
file and show you problem I run into?

"Joel" wrote:

Sub splitdata()


Sh2LastCol = 1
With Sheets("Sheet1")
.Rows(1).Insert 'add blank row so rows in sheet 1 & 2 are the same
RowCount = 2
Do While .Range("A" & RowCount) < ""
ColCount = 1
Do While .Cells(RowCount, ColCount) < ""
Category = Trim(.Cells(RowCount, ColCount))
Data = Trim(.Cells(RowCount, ColCount + 1))
With Sheets("Sheet2")
Set c = .Rows(1).Find(what:=Category, _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
.Cells(1, Sh2LastCol) = Category
.Cells(RowCount, Sh2LastCol) = Data
Sh2LastCol = Sh2LastCol + 1
Else
.Cells(RowCount, c.Column) = Data
End If
End With
ColCount = ColCount + 2
Loop
RowCount = RowCount + 1
Loop
End With
End Sub

"Mark" wrote:

The format looks like this

A B C D
E F
Invoice #: 12345 Invoice Date: 1/16/1950 A/P Code: ABC
Due Date: 1/16/1950 Total Payable: $20.00 Reference: Freight:
Account #: 1234 Description: Name Reference Amount: 10.00
Account #: 4321 Description: Name Reference 2 Amount: 10.00

The detail table I would like to be able to produce is Invoice, Invoice
data, AP Code, Due Date, Account #, Description, Amount. The biggest problem
I am running into is being able to match the Account # on A3 to the
information on A1:F2. Another problem that could arise is there could
possible be up to 12 different account numbers. Do you have any coding
examples for this? I am not the greatest at writing VBA.

"Joel" wrote:

The right way to do this task is to have a unique column for each field. A
macro can be writen that looks at each line and automatically moves the data
to a unique column in a new worksheet. It is hard to tell from the posting
exactly how to write the code because I can't tell which data is in which
columns.

the new worksheet would look something like this on row 1 (the commas
showing a different column). Will create the header row with the columns and
put the data in the corrrect column.

Invoice #, Invoice Date, A/P Code, Due Date, Total Payable,
Reference Freight, Account #, Description, Name Reference, Name
Referrence 2,

"Mark" wrote:

Is there any formula that I could use to be able to sort the below
information into a format that I could create a pivot table on?

Invoice #: 12345 Invoice Date: 1/16/1950 A/P Code: ABC
Due Date: 1/16/1950 Total Payable: $100.00 Reference: Freight:
Account #: 1234 Description: Name Reference Amount: $100.00
Account #: 4321 Description: Name Reference 2 Amount: $100.00

Any help would be greatly appreciated