Excel 2007 - Data breakout help 1

[ITALIAORIANA]

Hi,

I have an excel spreadsheet that I need to break out the data. I have 18 columns of information. The row count is variable. I need to break out the data as follows. Is there a better way to get this data broken out? The way I did it with the formula may be a convoluted way of doing this so any suggestions would be greatly appreciated.

A1 value with C1, C2, C3, etc.....all other data in each column would be exactly the same. Then I would need A2 value with C1, C2, C3, etc.


ABCD, 1/1/2007 0:00, ABCD, 12/31/9999 0:00, ZERO, N, N, 0, 0, 0, 0, 1, 1753-01-01 00:00:00.000
DEF6, 1/1/2007 0:00, DEF6, 12/31/9999 0:00, ZERO, N, N, 0, 0, 0, 0, 1, 1753-01-01 00:00:00.000
3FRJ, 1/1/2007 0:00, 3FRJ, 12/31/9999 0:00, ZERO, N, N, 0, 0, 0, 0, 1, 1753-01-01 00:00:00.000
ABC6, 1/1/2007 0:00, ABC6, 12/31/9999 0:00, ZERO, N, N, 0, 0, 0, 0, 1, 1753-01-01 00:00:00.000
SED8, 1/1/2007 0:00, SED8, 12/31/9999 0:00, ZERO, N, N, 0, 0, 0, 0, 1, 1753-01-01 00:00:00.000
JKL5, 1/1/2007 0:00, JKL5, 12/31/9999 0:00, ZERO, N, N, 0, 0, 0, 0, 1, 1753-01-01 00:00:00.000

I used the formula below to get it to work for A1 with C1 through C7 but I wasn't able to get it to work for A2 and beyond.

(=$A$2&" "&TEXT(B2,"m/d/yyyy")&" "&C2&" "&TEXT(D2,"m/d/yyyy")&" "&E2&" "&F2&" "&G2&" "&H2&" "&I2&" "&J2&" "&K2&" "&L2&" "&M2&" "&N2&" "&O2&" "&P2&" "&Q2&" "&R2)


Thanks
Deana

 

[mintjulep]

A1 value with C1, C2, C3, etc

Then I would need A2 value with C1, C2, C3, etc.

So C1, C2, C3, etc. is the same always?

Why try to create the mess each time?

Create it once. Use it many.

 

[Andrzejek]

Is this your data in columns A-M:
[pre]
A B C D E F G H I J K L M
ABCD, 1/1/2007 0:00, ABCD, 12/31/9999 0:00, ZERO, N, N, 0, 0, 0, 0, 1, 1753-01-01 00:00:00.000
DEF6, 1/1/2007 0:00, DEF6, 12/31/9999 0:00, ZERO, N, N, 0, 0, 0, 0, 1, 1753-01-01 00:00:00.000
3FRJ, 1/1/2007 0:00, 3FRJ, 12/31/9999 0:00, ZERO, N, N, 0, 0, 0, 0, 1, 1753-01-01 00:00:00.000
ABC6, 1/1/2007 0:00, ABC6, 12/31/9999 0:00, ZERO, N, N, 0, 0, 0, 0, 1, 1753-01-01 00:00:00.000
SED8, 1/1/2007 0:00, SED8, 12/31/9999 0:00, ZERO, N, N, 0, 0, 0, 0, 1, 1753-01-01 00:00:00.000
JKL5, 1/1/2007 0:00, JKL5, 12/31/9999 0:00, ZERO, N, N, 0, 0, 0, 0, 1, 1753-01-01 00:00:00.000
[/pre]

Could you show what your output should look like?

Have fun.

---- Andy

 

[ITALIAORIANA]

Yes, my data is column A through M. I need to break it out like the following:

A, B, C, D, E, F, G, H, I, J, K, L, M
ABCD, 1/1/2007 0:00, ABCD, 12/31/9999 0:00, ZERO, N, N, 0, 0, 0, 0, 1, 1753-01-01 00:00:00.000
ABCD, 1/1/2007 0:00, DEF6, 12/31/9999 0:00, ZERO, N, N, 0, 0, 0, 0, 1, 1753-01-01 00:00:00.000
ABCD, 1/1/2007 0:00, 3FRJ, 12/31/9999 0:00, ZERO, N, N, 0, 0, 0, 0, 1, 1753-01-01 00:00:00.000
ABCD, 1/1/2007 0:00, ABC6, 12/31/9999 0:00, ZERO, N, N, 0, 0, 0, 0, 1, 1753-01-01 00:00:00.000
ABCD, 1/1/2007 0:00, SED8, 12/31/9999 0:00, ZERO, N, N, 0, 0, 0, 0, 1, 1753-01-01 00:00:00.000
ABCD, 1/1/2007 0:00, JKL5, 12/31/9999 0:00, ZERO, N, N, 0, 0, 0, 0, 1, 1753-01-01 00:00:00.000



A, B, C, D, E, F, G, H, I, J, K, L, M
DEF6, 1/1/2007 0:00, ABCD, 12/31/9999 0:00, ZERO, N, N, 0, 0, 0, 0, 1, 1753-01-01 00:00:00.000
DEF6, 1/1/2007 0:00, DEF6, 12/31/9999 0:00, ZERO, N, N, 0, 0, 0, 0, 1, 1753-01-01 00:00:00.000
DEF6, 1/1/2007 0:00, 3FRJ, 12/31/9999 0:00, ZERO, N, N, 0, 0, 0, 0, 1, 1753-01-01 00:00:00.000
DEF6, 1/1/2007 0:00, ABC6, 12/31/9999 0:00, ZERO, N, N, 0, 0, 0, 0, 1, 1753-01-01 00:00:00.000
DEF6, 1/1/2007 0:00, SED8, 12/31/9999 0:00, ZERO, N, N, 0, 0, 0, 0, 1, 1753-01-01 00:00:00.000
DEF6, 1/1/2007 0:00, JKL5, 12/31/9999 0:00, ZERO, N, N, 0, 0, 0, 0, 1, 1753-01-01 00:00:00.000


A, B, C, D, E, F, G, H, I, J, K, L, M
3FRJ, 1/1/2007 0:00, ABCD, 12/31/9999 0:00, ZERO, N, N, 0, 0, 0, 0, 1, 1753-01-01 00:00:00.000
3FRJ, 1/1/2007 0:00, DEF6, 12/31/9999 0:00, ZERO, N, N, 0, 0, 0, 0, 1, 1753-01-01 00:00:00.000
3FRJ, 1/1/2007 0:00, 3FRJ, 12/31/9999 0:00, ZERO, N, N, 0, 0, 0, 0, 1, 1753-01-01 00:00:00.000
3FRJ, 1/1/2007 0:00, ABC6, 12/31/9999 0:00, ZERO, N, N, 0, 0, 0, 0, 1, 1753-01-01 00:00:00.000
3FRJ, 1/1/2007 0:00, SED8, 12/31/9999 0:00, ZERO, N, N, 0, 0, 0, 0, 1, 1753-01-01 00:00:00.000
3FRJ, 1/1/2007 0:00, JKL5, 12/31/9999 0:00, ZERO, N, N, 0, 0, 0, 0, 1, 1753-01-01 00:00:00.000


I need this information broken down like this for each value that is in Column C. I am just wracking my brain trying to figure out how to get the data broken out.

Thanks
Deana

 

[SkipVought]

hi,

This can quite simply be done in MS Query, with a cartesian join.

Got results in about 15 seconds

The source data is on Sheet2. On Sheet 3 is...
[tt]
A
ABCD
DEF6
3FRJ
ABC6
SED8
JKL5
[/tt]

SELECT `Sheet3$`.A, `Sheet2$`.B, `Sheet2$`.C, `Sheet2$`.D, `Sheet2$`.E, `Sheet2$`.F, `Sheet2$`.G, `Sheet2$`.H, `Sheet2$`.I, `Sheet2$`.J, `Sheet2$`.K, `Sheet2$`.L, `Sheet2$`.M
FROM
  `C:\tt cartesian.xlsx`.`Sheet2$` `Sheet2$`
, `C:\tt cartesian.xlsx`.`Sheet3$` `Sheet3$`

faq68-5829

Skip,

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

 

[ITALIAORIANA]

Skip,

It took me a bit to get this to work, because I have never even heard of cartesian joins before so I had to google it to get a better understanding. THIS IS FABULOUS!!! Thank you so much for your help. The files that were coming in were small until recently so copying, pasting, and changing the data was a viable option. This new file is 4,000 rows! You have saved me a tremendous amount of work, and now learning about the cartesian joins has been a HUGE asset because I can already see where this will be useful for other things.

If I could give you 5,000 stars for this I would.

THANK YOU SO MUCH!!!


Deana

 

[SkipVought]

For the sake of members who may browse this thread...

Cartesian join
From Oracle FAQ

cartesian join is a join of every row of one table to every row of another table. This normally happens when no matching join columns are specified. For example, if table A with 100 rows is joined with table B with 1000 rows, a cartesian join will return 100,000 rows. Something USUALLY to be avoided!

Note: A query must have at least (N-1) join conditions to prevent a cartesian product, where N is the number of tables in the query.

Emphasis addded




Skip,

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