Text To Column Question

T

thecubemonkey

Hi everyone,

I'm hoping someone can help me...here goes. I have a spreadsheet
similar to this (this is obviously a sample but similar to the real
thing):

PetID Medication Dosage
0001 Anti-itch 10mg
0001 IV Bag 1000mg
0001 Vitamin shot 200mg
0002 Anti-itch 20mg
0002 IV Bag 6000mg
0002 Vitamin shot 100mg

Note that the petIDs are repeated because one pet can have many
dedication at any dosage.

I'd like the spreadsheet to look like this:

PetID Anti-inch IV Bag Vitamin Shot
0001 10mg 1000mg 200mg
0002 20mg 600mg 100mg

Any way to do this easily? I have over 600 records and the thought of
manually doing this nauseates me!

Thanks in advance.
TCM
 
G

Guest

Create a helper column DosID in column D with formula =A2&B2, dragged down
as required.
Create a pivot table on a new sheet with PetID as row, Medication as column,
Dosage as data.
Select all cells in pivot table sheet, Copy and PasteSpecial(Values) them in
a new sheet.
Delete unnecessary total row an column.
Enter this formula in cell B4 (at intersection of 0001 row and Anti-itch
column):
=INDEX(Sheet1!$C:$C,MATCH($A5&B$4,Sheet1!$D:$D,0))
Sheet1 being your original sheet.
Drag formula to the right and down as required.


Regards,
Stefi

„[email protected]†ezt írta:
 
G

Guest

Hi,

Do the following:

1. On the Data menu, point to Filter, and then click AutoFilter.
2. Delete the Medication and Dosage header
3. Select the range of 0001 as shown below:

PetID
0001 Anti-itch 10mg
0001 IV Bag 1000mg
0001 Vitamin shot 200mg

4. On the Edit menu, click Copy
5. Click a cell as a new location.
6. On the Edit mneu, click Paste Special. The paste Special dialog box
appears.
7. Click Tanspose check box and then click the OK to return to the Worksheet.

PetID 0001 0001 0001
Anti-itch IV Bag Vitamin shot
10mg 1000mg 200mg
0002 0002 0002
Anti-itch IV Bag Vitamin shot
20mg 6000mg 100mg

8. Clean the structure.

Challa Prabhu
 
Top