Database and VBA

  • Thread starter Thread starter Howard
  • Start date Start date
H

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
 
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

Howard said:
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
Click to expand...

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

The message was checked by ESET Smart Security.

http://www.eset.com
 
Back
Top