Data Manipulation

dp313

New Member
Joined
Jan 27, 2005
Messages
8
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
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Barry Katcher

Well-known Member
Joined
Feb 25, 2002
Messages
4,053
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
ABCD
1IDProductProduct2
21bookpen
32penpen2
43desk
51pen
62pen2
730
Sheet1


Barry
 

dp313

New Member
Joined
Jan 27, 2005
Messages
8
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
 

Forum statistics

Threads
1,148,524
Messages
5,747,183
Members
424,068
Latest member
Salim khamis

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top