I want to merge two Excel sheets. One has 2004 prices for 6000 products (identified by type code) with descriptions. The other has 2005 prices with type code but without descriptions and with part codes for 9000 products. Some parts in 2004 are not in 2005 and vice versa. I want to end up with all the type codes, their descriptions (where available), their part code (where available), their 2004 price and their 2005 price. I posted this query last july and Ken and Skip helped. I tried Kens solution (Pivot Table) with just three columns and it worked. But when I added another column, I ended with duplicate rows where there was blank cells in input data. I could not get Skips Table solution to work as I'm not familiar with creating Tables in Excel. Solving this would really make my YEAR.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Excel Merging 1
- Thread starter franku
- Start date
-
1
- #2
Hi,
I'd go with a pivot table on two columns of data - PART CODE and PART DESCRIPTION from BOTH tables (2004&2005) on top of each other.
Put both fields in ROW in the pivot table layout form, and whatever in DATA (like Count of Part etc), then finish.
Now you'll have a 'totals' row between every part - highlight these rows by clicking in the column break to the left of the 'A' column (the mouse pointer should look like an arrow pointing right).Rightclick, select 'Hide'.
Now you have a pivot table showing PART, PART DESCRIPTION and then the 'whatever'-field mentioned above. As long as you don't have different desciptions in the two tables (2004 / 2005), then you should now recieve a lot of 'blank' values - to get rid of them, click the arrow on the column header, and uncheck 'blank'.
Copy the entire sheet, paste-special: values to remove the pivot table. Delete the 'whatever'-column.
Now you have a list with what you wanted, I believe. Now you can easily VLOOKUP prices from the two tables if you like.
A tip would be to add two columns to your new table: YEAR and PRICE. Enter '2004' as year, copy all rows, paste beneath the existing rows, change them to 2005. Now you can VLOOKUP from the yearly tables, and next year you can simply add new fields in your database table. By treating your data this way you can easily query information, by functions, pivot tables or SQL (access etc).
Hope it helps,
// Patrik
______________________________
To the optimist, the glass is half full. To the pessimist, the glass is half empty. To the IT Professional, the glass is twice as big as it needs to be.
I'd go with a pivot table on two columns of data - PART CODE and PART DESCRIPTION from BOTH tables (2004&2005) on top of each other.
Put both fields in ROW in the pivot table layout form, and whatever in DATA (like Count of Part etc), then finish.
Now you'll have a 'totals' row between every part - highlight these rows by clicking in the column break to the left of the 'A' column (the mouse pointer should look like an arrow pointing right).Rightclick, select 'Hide'.
Now you have a pivot table showing PART, PART DESCRIPTION and then the 'whatever'-field mentioned above. As long as you don't have different desciptions in the two tables (2004 / 2005), then you should now recieve a lot of 'blank' values - to get rid of them, click the arrow on the column header, and uncheck 'blank'.
Copy the entire sheet, paste-special: values to remove the pivot table. Delete the 'whatever'-column.
Now you have a list with what you wanted, I believe. Now you can easily VLOOKUP prices from the two tables if you like.
A tip would be to add two columns to your new table: YEAR and PRICE. Enter '2004' as year, copy all rows, paste beneath the existing rows, change them to 2005. Now you can VLOOKUP from the yearly tables, and next year you can simply add new fields in your database table. By treating your data this way you can easily query information, by functions, pivot tables or SQL (access etc).
Hope it helps,
// Patrik
______________________________
To the optimist, the glass is half full. To the pessimist, the glass is half empty. To the IT Professional, the glass is twice as big as it needs to be.
- Thread starter
- #3
patrik, tried your suggestion. I have two lists, Both have type codes. One has 2004 prices and descriptions but no ident numbers. The other has 2005 prices and ident numbers but no descriptions. When I do as you suggest, I end with list of type codes which has a line for 2004 price, description and (blank)for ident number. The second line for each type code contains 2005 price and ident number with (blank) description. If I hide say the blanks for description, I lose all the entries for 2005 prices. If I hide the blank for ident number, I lose the 2004 prices.
AnotherHiggins
Technical User
Can you provide a small sample of the data you are dealing with? That will help resolve your problem.
[tt]_____
[blue]-John[/blue]
[/tt][red]"If you're flammable and have legs, you are never blocking a fire exit."[/red]
-Mitch Hedberg
Help us help you. Please read FAQ181-2886 before posting.
[tt]_____
[blue]-John[/blue]
[/tt][red]"If you're flammable and have legs, you are never blocking a fire exit."[/red]
-Mitch Hedberg
Help us help you. Please read FAQ181-2886 before posting.
- Thread starter
- #5
Sample of data type:
YEAR Type Description PartNo Price
2004 a123 Gadget123 19.21
2004 a125 Gizmo 125 57.53
2004 b321 Thingy 532 128.96
2005 a123 100123 19.98
2005 a124 100124 87.22
2005 a125 999125 57.88
2005 b321 877123 129.33
The 2005 prices have part numbers, the 2004 have descriptions, both have type codes but some in 2004 are not in 2005 and vice versa. I want all types with description and both prices. Sorry I cant see replies from skip and xlbo as they seem to have disappeared.
YEAR Type Description PartNo Price
2004 a123 Gadget123 19.21
2004 a125 Gizmo 125 57.53
2004 b321 Thingy 532 128.96
2005 a123 100123 19.98
2005 a124 100124 87.22
2005 a125 999125 57.88
2005 b321 877123 129.33
The 2005 prices have part numbers, the 2004 have descriptions, both have type codes but some in 2004 are not in 2005 and vice versa. I want all types with description and both prices. Sorry I cant see replies from skip and xlbo as they seem to have disappeared.
Take the list (PT) you have, turn into values (copy, paste special: values), add two columns: TYPE2 and DESCRIPTION2
In my example below the columns are set as follows: YEAR = Col A, TYPE = Col B, Desc = Col C, PARTNO = Col D, PRICE = Col E, TYPE2 = Col F, DESCRIPTION2 = Col G
I'm also assuming 2004 values end at row 500, and 2005 end at row 1000. I'm also assuming that you are entering the functions on row 2.
Now, in TYPE2 (Col F), use the following:
=VLOOKUP(B2,$B$1:$D$500,2,FALSE)
fill down all rows
In DESCRIPTION2 (Col G), use the following:
=VLOOKUP(B2,$B$501:$D$1000,3,FALSE)
fill down all rows
Now select the entire table, create a new PT, but use TYPE2 and DESCRIPTION2 instead of the first ones.
// Patrik
______________________________
To the optimist, the glass is half full. To the pessimist, the glass is half empty. To the IT Professional, the glass is twice as big as it needs to be.
In my example below the columns are set as follows: YEAR = Col A, TYPE = Col B, Desc = Col C, PARTNO = Col D, PRICE = Col E, TYPE2 = Col F, DESCRIPTION2 = Col G
I'm also assuming 2004 values end at row 500, and 2005 end at row 1000. I'm also assuming that you are entering the functions on row 2.
Now, in TYPE2 (Col F), use the following:
=VLOOKUP(B2,$B$1:$D$500,2,FALSE)
fill down all rows
In DESCRIPTION2 (Col G), use the following:
=VLOOKUP(B2,$B$501:$D$1000,3,FALSE)
fill down all rows
Now select the entire table, create a new PT, but use TYPE2 and DESCRIPTION2 instead of the first ones.
// Patrik
______________________________
To the optimist, the glass is half full. To the pessimist, the glass is half empty. To the IT Professional, the glass is twice as big as it needs to be.
- Status