Create an import file based on another file

[Maureen405]

I have a spreadsheet that that tracks daily sales by location and payment type. (Cash, Check, Credit Card) I am looking to use this spreadsheet to create an import. Any advice is greatly appreciated. Thanks.

Maureen

From This:

Location Date Cash Check Credit
Site 1 9/1/2014 5.00 10.00 20.00
Site 2 9/1/2014 10.00 20.00 30.00
Site 3 9/1/2014 20.00 30.00 40.00

To This:

Site 1 9/1/2014 5.00 cash
Site 1 9/1/2014 10.00 check
Site 1 9/1/2014 20.00 credit
Site 2 9/1/2014 10.00 cash
Site 2 9/1/2014 20.00 check
Site 2 9/1/2014 30.00 credit
Site 3 9/1/2014 20.00 cash
Site 3 9/1/2014 30.00 check
Site 3 9/1/2014 40.00 credit

[p24leclerc]

Why do you want to do this?

[Maureen405]

The "from" is an Access Database export into Excel. the "to" is the format that I need in order to import the transactions into our General Ledger system.

[trisoldee]

If formatting is always consistent you could just set what you want to be the import as a separate table and put a vlookup in the cell where you need the amount. You would basically just have to change the col index number to return the cash column(3), check column(4), or credit column(5). Because it's for an import you'd then probably want to copy and paste as values in a new workbook.

[p24leclerc]

See attached workbook.
You must be IN the sheet that contains the FROM data to run the Create_Import macro.
The sheet named Import will be filled with formatted data ready for your import.
The macro will clear all contents of sheet Import before copying data.

[Maureen405]

p24leclerc - You Rock! Just what I was looking for!