sort/format this spreadsheet

[Matt Houston]

hi, following the failure of my last post....hoping somebody here can
help me with this.

i have a spreadsheet that looks something like this (except with about
10 000 rows)

DOT1 AGENT1 NAME1A
DOT1 AGENT1 NAME1B
DOT1 AGENT1 NAME1C
DOT1 AGENT1 NAME1D
CLPM AGENT2 NAME2A
CLPM AGENT2 NAME2B
U788 AGENT3 NAME3A
U788 AGENT3 NAME3B
U788 AGENT3 NAME3C

and i need it to look like this

DOT1 AGENT1 NAME1A NAME1B NAME1C NAME1D
CLPM AGENT2 NAME2A NAME2B
U788 AGENT3 NAME3A NAME3B NAME3C

bearing in mind that there can be anywhere between 1 and 30 names.
possible? can anybody help? would really appreciate it.

 

[Jane]

This is really roundabout and could be confusing, but here
is what I would do.

1. Insert a new column concantenating A & B (i.e., =A1&B1,
so the answer comes out DOT1AGENT1.)
2. Sort by this column. I'm going to assume it's C.
3. Insert another column at D and put the number 1 in D1.
In D2, put the formula =IF(C2=C1,C1+1,1) Copy this down.
This will create a number for each instance of Name by
Agent.
4. Insert another column at E concatenating C&D, so the
answer reads DOT1AGENT11, DOT1AGENT12, DOT1AGENT13, etc.
5. Copy and paste columns A, B and C in a new worksheet.
6. Put the number 1 in D1 and in D2, put the formula = if
(c2=c1,0,1). Copy the formula down, then copy the column
and do a paste special . . . Values.
7. Sort by column D and delete the 0 values. You should
now have only one entry for each unique combination of
columns A&B. Delete columns C & D.
8.For headers for columns C and up, put the numbers 1 - 30
(you said there could be up to 30 names per agent).
9.In C2, put the formula =VLOOKUP
($A2&$B2&C&1,AREA_REF,2,FALSE). AREA_REF is the table
from the old worksheet, with the concatenated formula from
step 4 in the left row. The actual reference will be
something like 'Old Sheet'!$E$2:$F$1000.
10. Copy this formula across and down.

That's it. Confused?


Jane