How to format columns to row type data...like a transform

[Pack10]

We have a spreadsheet consisting of trade info. Rows for trade_type, buy qty,
buy_LMV, buy_total and sell qty, sell_SMV, sell_total

XXX S 100 34,500 50,000
XXX B 200 40,000 52,000

XXY S 100 20,000 33,500
XXZ S 400 18,000 32,500
XXZ B 100 9,000 16,000

We would like to format it so it appears like ....
XXX S 100 34,500 50,000 B 200 40,000 52,000
XXY S 100 20,000 33,000
XXZ S 400 18,000 16,000 B 100 9,000 16,000

Anyone know how to do it, couln not get the transpose to work.

Thx

 

[SkipVought]

hi,

1) put heading on your table

2) using MS Query via Data > Import query

So what are the headings for your table?

What are the headings for the result of the query?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Pack10]

I am a little confused here. I have a spread sheet that was put together by someone, I am trying to "reformat it". Why MS Query?
Essentially I have "buy data" and "Sell data" and we want it on one line instead of multiple. How do I use this spreadsheet with MS Query.

 

[SkipVought]

Well you could cut 'n' paste ON that sheet if you choose.

That's not what i would do, especially if this were to be a resurring issue or it there were lots of rows to do this for.

A query would return data to a new range, on an empty sheet.

Other than that, you would need to code a VBA macro, which I would not recommend.

If this is a ONE TIME deal, you could 1) code some spreadsheet functions in EMPTY COLUMNS to get the B data, 2) COPY 'n' Paste Special -- VALUES, 3) autofilter on the B values in column B, I assume, and 4) DELETE the visible rows. But I'd only do this if you were sure that it will be a single occurrence.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Pack10]

I have never used the query tool inside of Excel.
I have columns ACCT, FAM, NAME, B/S, BL, TRD, QTY, FIRST AMT.
I need it to look like this

ACCT FAM NAME B/S BL TRD QTY FIRST_AMT B/S BL TRD QTY FIRST_AMT
--------------------------------------- -------------------------

So, how can I do this in Excel using MS Query.

Thanks so much.

 

[SkipVought]

I need to know exactly which data is in each column and exactly what data needs to go into each of the adjacent columns.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

BTW, your original example seems to look NOTHING like the headings you just posted.

You must be

Clear, Concise and Complete.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Pack10]

The spreadsheet contains the following columns.

GRP GRP NAME OCCD OCCD NAME FAM FAM NAME

REG ENTRY CD B/S BL TRD QUANTITY

FIRST AMT(U$) CUST AMT(U$) OTH AMT(U$) LAST AMT

An account could have 1 line containing buy data or 1 line consisting of sell data, or multiple lines containing buy and sell data. So the following fields contain 2 sets of B/S BL TRD QTY FIRST AMT....

GRP GRP NAME OCCD OCCD NAME FAM FAM NAME

REG ENTRY CD B/S BL TRD QUANTITY

FIRST AMT(U$) CUST AMT(U$) OTH AMT(U$) LAST AMT

B/S BL TRD QUANTITY

FIRST AMT(U$) CUST AMT(U$) OTH AMT(U$) LAST AMT

 

[SkipVought]

I need to see sample data and an example, using that sample data, of what you attempted to explain above.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Your column example is totally incomprehensible!!!!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Holy mackerel!

You've gone from ....
[tt]
trade_type, buy qty, buy_LMV, buy_total and sell qty, sell_SMV, sell_total
[/tt]
to ...
[tt]
ACCT, FAM, NAME, B/S, BL, TRD, QTY, FIRST AMT
[/tt]
to ...
[tt]
GRP GRP NAME OCCD OCCD NAME FAM FAM NAME

REG ENTRY CD B/S BL TRD QUANTITY

FIRST AMT(U$) CUST AMT(U$) OTH AMT(U$) LAST AMT

[/tt]
Are you high on something?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}