Denormalize two tables or datasheets

[weskyleo]

Hello Folks, I'm trying to denormalize two data tables . The sets are as follows

CompId CompDiv
100 A
100 B
101 X
101 Y
102 Z

AND

CompId OrderType Qty
100 OfficeSupp 88
101 Computer 5

My output should look like the following ;



CompId CompDiv OrderType Qty
100 A OfficeSupp 88
100 B OfficeSupp 88
101 X Computer 5
101 Y Computer 5
101 Z Computer 5

Any help is much appreciated

Thanks
leo

 

[shadow12345]

Could you try to explain how you arrive at that output?

Are your saying combined output table
Show all CompID and CompDiv data
Then Look in table two for The comp ID find it and put what comes in Order type & quantity in table 2

Also what happens to 102 Z ... i don't see that anyway in the combined table.

 

[Fazza]

Using a couple of defined names - it isn't necessary but makes explanation easier of tblA and tblB - then save the file. Query via ALT-D-D-N & follow the wizard. Select one or both tables as the data source & continue taking the option to edit in MS Query. If you don't already see the second table add it now. Drag & join between the tables on the CompID field. See the results dataset. Deleted the second CompID field. 'Open door' icon to exit MS Query & load finished query table to a worksheet. It is refreshable like a pivot table. SQL might be like below, btw. HTH. regards

SELECT A.CompId, A.CompDiv, B.OrderType, B.Qty
FROM tblA A, tblB B
WHERE A.CompId = B.CompId

 

[weskyleo]

@shadow12345 - Sorry for the typo . 102 is actually 101.
I'm trying to do a what is commonly known as JOIN in Query World. But here in this case I'm trying to do using excel
@Fazza - I'm trying your solution but somehow when I put the data into tables in excel file (2 spreadsheets with 1 table each) , and try to pull them in a third sheet using the option of Alt-D, D-N like you mentioned, I'm getting the message that there are no active tables in those sheets. So , I dont know how to go about doing this...any idea? Missing something?

Thanks much for your help
Leo

 

[weskyleo]

Actually the message says ...
The database contains no visible tables

 

[Norie]

If this is in Excel couldn't you just use formulas?

eg =INDEX(B$2:B$3, MATCH($F2,$A$2:$A$3))

Where the first table is in A1:B3 and the second in F1:G6 and the formula in H2 and copied across and down.

By the way I assume the 102 is a typo.

In Access the 'join' query would look like this:

SELECT Sheet21.CompId, Sheet21.CompDiv, Sheet2.OrderType, Sheet2.Qty
FROM Sheet21 LEFT JOIN Sheet2 ON Sheet21.CompId = Sheet2.CompId;

 

[Fazza]

hi, Leo

Norie's SQL is equivalent: please use either.

Using a couple of defined names - it isn't necessary but makes explanation easier of tblA and tblB - then save the file.

Using defined names avoids the message about no visible tables.

Otherwise, when there is the message about no visible tables, go via 'options' and then 'system tables'. Now you don't need to use the defined name approach - assuming your data headers are in row 1 and then have data immediately under.

With two separate source files I think it is easiest to handle within the SQL by qualifying the file paths & names. It might be something like below.

regards

SELECT A.CompId, A.CompDiv, B.OrderType, B.Qty
FROM `C:\path\filename.xls`.[YourFirstSheetName$] A, `D:\path\filename.xls`.[YourOtherSheetName$].tblB B
WHERE A.CompId = B.CompId

 

[weskyleo]

@Norie- =INDEX(B$2:B$3, MATCH($F2,$A$2:$A$3)) formula does not seem to work , Are you trying to get obtain A, B (divisons) ..? My table1 is from A1:B6 including headers and table2 is from F1 to H3 including headers

@Fazza- I named the tables , still not visible . I dont see where the option->sys tables is /??? how to find it?
Sorry for sounding ignorant...I dont know how to write SQL in Excel...can you please point to what I should be looking at or how do go about doing this

Thank you guys . I appreciate it much

Leo

 

[Fazza]

Maybe http://www.dicks-clicks.com/excel/ExternalData.htm

Google will find more. Post again if required. I can post more detail of each step (when using the wizard). Sorry that I've been brief on this, it is something I've posted many, many times. Google with site:mrexcel.com will find old forum posts. cheers

 

[Fazza]

If there is a problem somehow (which I guess is not the case, it is just unfamiliarity with the technique), this might help? http://www.dailydoseofexcel.com/archives/2007/04/17/cannot-find-the-input-table/

Or otherwise start again from a new file.