Excel - How to transpose / copy with the opposite values 1

[arvarr]

Hi
Can someone assist with the following query?

I would like to see if it is possible to achieve the following:
From Worksheet 1:
I would like to transpose / copy all rows and columns onto Worksheet 2 with same rows and also make reversing entries.
See below for example:

Worksheet 1
Description Code 1 Code 2 Code 3 Amount
Software Cost 1234 abc def 100.00
Accruals 2345 ghi jkl (100.00)
Fees 3456 mnl opq (100.00)
Accruals 4567 rst uvw 100.00


Worksheet 2
Description Code 1 Code 2 Code 3 Amount
Software Cost 1234 abc def 100.00
Accruals 2345 ghi jkl (100.00)
Accruals 2345 ghi jkl (100.00)
Software Cost 1234 abc def 100.00
Fees 3456 mnl opq (100.00)
Accruals 4567 rst uvw 100.00
Accruals 4567 rst uvw (100.00)
Fees 3456 mnl opq 100.00

Thanks guys.

Cheers,

Arv

 

[SkipVought]

Hi,

You posted this question in two different forums!

This is frowned upon.

Please delete one or the other.

I see rows copied, but I see no transpositions or reversals???

Please state the logic for transpose/copy/reversing. It makes no sense to me.

Also, please use TGML tags for readability...
[pre]
Worksheet 1
Description Code 1 Code 2 Code 3 Amount
Software Cost 1234 abc def 100.00
Accruals 2345 ghi jkl (100.00)
Fees 3456 mnl opq (100.00)
Accruals 4567 rst uvw 100.00
[/pre]

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[arvarr]

Apologies Skip, i have deleted the other post.

The purpose is on worksheet 1, these are the data entries provided the users to enter some journal entries.

On Worksheet 2, I need to achieve the following:
1. Copy the exact original entries from Worksheet 1 into Worksheet 2
2. In addition, I need additional entries in Worksheet 2, which reflects reversing dollar entries (Consider it as a reversing journal)

Hope that makes sense.

Thanks.

Cheers,

Arv

 

[SkipVought]

???

Right off I see ONE Software with 100 in Worksheet 1 and the in Worksheet 2 I see TWO Sostware, BOTH with 100?

Is that what you want?

Where’s the reversal?

I am TOTALLY befuddled!

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[arvarr]

Hi Skip
I have attached picture.
Hopefully it makes sense.

Thanks.

Cheers,
Arv

 

[SkipVought]

???

That's even worse!

Right off I see Software going from Expense 1 to Expense 1 & 2??? Where did

I see no reversal of Amount for Software???

Sorry! This is getting muddier! This is making even less sense!

Your clarification is an obfuscation!

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[arvarr]

Have revised the description.
Thanks.

Cheers,
Arv

 

[SkipVought]

My solution result
[pre]
Description Code 1 Code 2 Code 3 Amount
Software Cost 1234 abc def 100
Accruals 2345 ghi jkl -100
Fees 3456 mnl opq -100
Accruals 4567 rst uvw 100
Software Cost 1234 abc def -100
Accruals 2345 ghi jkl 100
Fees 3456 mnl opq 100
Accruals 4567 rst uvw -100
[/pre]

My solution using MS Query via Data > Get External Data > From Other Sources > From Microsoft Query > Excel Files* --- and drill down to your workbook.

My workbook has Sheet1 and Sheet2. Yours should too.

Sheet1 hass your initial table

Sheet2 is where you add a QueryTable via the above sequence in the Data TAB.

Here is the SQL
[pre]
SELECT `Sheet1$`.Description, `Sheet1$`.`Code 1`, `Sheet1$`.`Code 2`, `Sheet1$`.`Code 3`, `Sheet1$`.Amount
FROM `Sheet1$` `Sheet1$`
union all
SELECT `Sheet1$`.Description, `Sheet1$`.`Code 1`, `Sheet1$`.`Code 2`, `Sheet1$`.`Code 3`, `Sheet1$`.Amount*-1
FROM `Sheet1$` `Sheet1$`
[/pre]

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[arvarr]

Thank you Skip, yes, it works.
I will have to try to test it on the larger set of data.

 

[SkipVought]

For future reference, transpose means something specific in Excel; something totally different from what you were referring to.


Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[SkipVought]

My uploaded workbook attached.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[arvarr]

Hi Skip
I forgot that on Worksheet 2, the format is slightly different.
There are some additional columns in Worksheet 2.
Hence, I need to be able to add the steps as above and slot them in the correct column.
Can we still use get external data as suggested?

If on Worksheet 1, there are other details such as comments or rows/columns, can we still use Get External Data?

Thanks.

Cheers,
Arv

 

[SkipVought]

Where does the data for these other columns come from?

Right now, and according to your initial request, the data for sheet2 all comes from sheet1.

You can add whatever columns you want to add, but if you need to UPDATE more data from sheet1 at some later time, all bets are off.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[arvarr]

Extra columns from Worksheet 2 are just the additional formats which is different from Worksheet 1.
Yes, for the initial request, it works like a charm. It was my bad that I forgot that on Worksheet 2, there are slightly different format.

Thanks.

 

[SkipVought]

No, its not just a different format.

Its NEW DATA!

BTW, what happens with Sheet1 data once you get the data into sheet2?

Does any new data ever get added to sheet1 that needs to get to sheet2 in the same way?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[arvarr]

Sheet 1 gets redundant after its added.
When the need arise, users will have to populate sheet 1 again and have the same information copied across to Worksheet 2 again as per above.

It will be a recurring process hence hoping to get something into place so it is reusable.

Consider Worksheet 1 as the template for data entry.

Thanks.

Cheers,
Arv

 

[SkipVought]

So you put new data into sheet1 and refresh the query and you’ve got all new data from sheet1 in sheet2 and all the additional data columns you want have the wrong data every time.

Just adding columns is not the answer, unless you want to redo all the data in these new columns everybtime you refresh with new data from sheet1.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[arvarr]

Data in sheet 2 - some can be populated with macros.

Some data in New columns will be different each time.

Yes will have to redo some data in New columns as some are free form.

Thanks.

Cheers,
Thanks Arv

 

[SkipVought]

 

[SkipVought]

Here is a new version of the workbook with the free form columns.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}