Data Manipulation

[dp313]

My excel sheet looks like this:

ID Product Product(2)
1 book pen
2 pen pen2
3 desk

I need to keep the same format but in a different sense more to look like this:

ID Product
1 book
1 pen
2 pen
2 pen2
3 desk

bascially in a sense i need to transpose it but i can't figure out how to do that. I have thousands of files and the person who gave me this excel sheet gave me it in a very funky format and now i need to change it around to import it into an access table for use online.

Any help would be appreciated. Thanks

 

[Barry Katcher]

Hi, dp. Welcome to the Bored.

Try this (not completely tested). First, enter the formulas in a5 and b5, then copy them down as far as needed. Then, select all the formulas, Copy and Paste Special>Values onto itself. Now that you've converted the formulas to values, you can now delete column C. Then, filter for 0 in column B (no Product2) and delete those rows. You can then sort by ID or Product name.

Book1
	A	B	C	D
1	ID	Product	Product2
2	1	book	pen
3	2	pen	pen2
4	3	desk
5	1	pen
6	2	pen2
7	3	0
Sheet1

Barry

 

[dp313]

Kind of lost me on what you were trying to do....

ID Product Product(2) Product(3) Product(4)
1 Book Book Pen Desk
2 Bed
3 Bed Book
4 Pen Pencil Pen

Each row has different amount of products.
Ultimately i need it
ID Product
1 Book
1 Book
1 Pen
1 Desk
2 Bed
3 Bed
3 Book
4 Pen
4 Pencil
4 Pen

I don' tknow if this can be accompl;ished or not