Database and VBA

[Howard]

I need to build a standard template which allows me to upload it into my
accounting software.

My sales raw data is as tabled in the below format: -
Debtor Location Item A Item B Item C
AAA Country B 1000 100 10
BBB Country A 2000 200 20
CCC Country C 3000 300 30
BBB Country B 4000 400 40
EEE Country E 5000 500 50
AAA Country A 6000 600 60

The upload format is as below: -
Debtors Item type Location Amount
AAA Item A Country B 1000
AAA Item B Country B 100
AAA Item C Country B 10
AAA Item A Country A 6000
AAA Item B Country A 600
AAA Item C Country A 60
BBB Item A Country A 2000
BBB Item B Country A 200
BBB Item C Country A 20

Cont’d

What is the best and fastest way so that i can tabulate the data in the
upload format from my sales raw data?

Please HELP!
Thanks..Howard

 

[Roger Govier]

Hi Howard

You can decompose the data using a Pivot Table.
There are a few stages to go through, but it will work.

Place your cursor in cell A1 of your source data as you have shown it
Data>Pivot Table>Multiple Consolidation Ranges>Next>I will create the page
fields>Next>
Select only data from Column B to E>Add>Next>
Layout>drag Row and Column of of the PT outline, leaving just Value (Count
of Value or Sum of Value)>OK>Finish

On the new page that appears>double click on the number under Total and a
new Sheet will appear wither data listed out.
Lets say this appears on Sheet5

Now, with your source data, cut column B and move to column A so your data
order is Debtor, Location, ItemA etc.
Repeat the steps as above, and you will end up with a list of data on
another new sheet say this is Sheet7

Cut the data from column A of Sheet5 and Insert at column C of Sheet7
Rename the column headings and Voila!!!
--
Regards
Roger Govier

I need to build a standard template which allows me to upload it into my
accounting software.

My sales raw data is as tabled in the below format: -
Debtor Location Item A Item B Item C
AAA Country B 1000 100 10
BBB Country A 2000 200 20
CCC Country C 3000 300 30
BBB Country B 4000 400 40
EEE Country E 5000 500 50
AAA Country A 6000 600 60

The upload format is as below: -
Debtors Item type Location Amount
AAA Item A Country B 1000
AAA Item B Country B 100
AAA Item C Country B 10
AAA Item A Country A 6000
AAA Item B Country A 600
AAA Item C Country A 60
BBB Item A Country A 2000
BBB Item B Country A 200
BBB Item C Country A 20

Cont’d

What is the best and fastest way so that i can tabulate the data in the
upload format from my sales raw data?

Please HELP!
Thanks..Howard



__________ Information from ESET Smart Security, version of virus
signature database 4755 (20100108) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 4755 (20100108) __________

The message was checked by ESET Smart Security.

http://www.eset.com

 

[JB]

With Formulas:

H2: =Offset(A$2,Int((Row()-2)/3),0)
I2: =Offset(B$2,Int((Row()-2)/3),0)
J2: =Offset(C$2,Int((Row()-2)/3),Mod(Row()-2,3))

http://cjoint.com/?bkiRqXDRD8

JB
http://boisgontierjacques.free.fr/

 

[JB]

With VBA:

http://cjoint.com/?bkiZkEjtKF


Sub TransformeLigneColonne()
ligne = 2
For Each c In Range("A2:A" & [A65000].End(xlUp).Row)
For J = 1 To 3
Cells(ligne, 10) = c
Cells(ligne, 11) = c.Offset(, 1)
Cells(ligne, 12) = Val(c.Offset(, J + 1))
ligne = ligne + 1
Next
Next c
End Sub


JB
http://boisgontierjacques.free.fr/