Help ! This one should make you think !

[radar2000]

Hi everyone.

I have a bit of a problem that I need your help with.

I have an excel spreadsheet which needs to be altered...Please see sample below:

Dealer ID# Rep ID# Name
12345 abc1 jake
12345 abc2 joe
12345 abc3 phil
56789 def1 bob
56789 def2 bill

I need to transfrom the above to show the following Dearler ID# in one row...for example:

Dealer ID# Rep ID# Name Rep ID# Name
12345 abc1 jake abc2 joe
56789 def1 bob def2 bill

Please note that there may be more than 25 Rep ID#'s under one dealership. I need to have this Rep info shown across one row per each Dealership ID#.

Anyones help would greatly be appreciated.

Thank you.





_________________
Radar2000

[ This Message was edited by: radar2000 on 2002-04-16 11:02 ]

[ This Message was edited by: radar2000 on 2002-04-16 11:03 ]

[cpod]

If your data starts in A2,B2,C3 then, starting in E2, put a unique list (you can get a unique is by using Advanced Filter - Data/Filter/AdvancedFilter) of the data in column A.

Then in F2:

=IF($E2=INDEX($A$2:$C$500,MATCH($E2,$A$2:$A$500,FALSE)+COLUMN()-6,1),OFFSET($A$1,MATCH($E2,$A$2:$A$500,FALSE)+COLUMN()-6,1) & " " & OFFSET($A$1,MATCH($E2,$A$2:$A$500,FALSE)+COLUMN()-6,2),"")

and copy across and down.

[radar2000]

Hi cpod.

Thanks for your quick reply !

My data does start in A2,B2,C2. Please note that in in cells A1,B1,C1 contain header info.

I tried the advanced filter option, but I don't think I'm doing this right.

As per your instructions I put the cell in E2 and proceeded to apply the advanced filter. My questions here, are as follows:

1) in the list range section of this filter, what should I select? Should I select columns A & E?

2) Do I need to put anything in the criteria range?

3) Should I check-off the unique records only box?


Thank you.

[cpod]

For the list range select only the data in column A including cell A1.

Put a check in the "Unique Records" check box.

Select "Copy to Another Location" and in the "Copy To" box select a range starting at E1 and big enough to hold the unique list such as E1:E500.

Leave the criteria range blank.

[radar2000]

Hi cpod.

Looks like the advanced filter worked. It basically created a second column A for me under column E. It also took away any duplicate ID#'s that were found under column A.

Now I pasted your formula in cell F2 and received a #Value! message.

Am I doing something wrong?

Thank you.

[cpod]

Is your data structured as in the example in your original post?

Does E2 contain the first value in your Dealer ID# list in column A?

[radar2000]

Hi cpod.

Yes it is.

Column A contains Dealer ID#'s
Column B contains Code ID#'s
Column C contains Name

After doing the advanced filter option, my column E now contains the Dealer ID# info.

Thank you.

[Aladin Akyurek]

Column A contains Dealer ID#'s
Column B contains Code ID#'s
Column C contains Name

After doing the advanced filter option, my column E now contains the Dealer ID# info.

Also, given the sample in A1:C6 including labels and the unique list in E,

in D1 enter:

=MATCH(9.99999999999999E+307,A:A)-ROW(1:1)

In F2 enter and copy across to G2:

=IF(LEN($E2),VLOOKUP(E2,OFFSET(A$2,0,0,$D$1,2),2,0),"")

in H2 enter:

=IF(AND(LEN($E2),COUNTIF(OFFSET($A$2,0,0,$D$1,1),$E2)>COUNTA($F2:G2)/2),INDEX(OFFSET($B$2,MATCH(F2,OFFSET($B$2,0,0,$D$1,1),0),0,$D$1,1),MATCH($E2,OFFSET($A$2,MATCH(F2,OFFSET($B$2,0,0,$D$1,1 ),0),0,$D$1,1),0)),"")

in I2 enter:

=IF(LEN(H2),VLOOKUP(H2,OFFSET($B$2,0,0,$D$1,2),2,0),"")

Select H2:I2 and copy across as far as needed, say, till column M.

Now select F2:M2 and copy down as far as needed.

The above schema does not require that the data be sorted. It can also dynamically cope with addtitions/deletions that may occur in the data range.

Just curious: Why do you want this rearrangemnet of data?

Aladin

[Yogi Anand]

Hi radar2000:
I think your project is also a good candidate for a pivot table solution -- you ought to try it to get different layouts and statistics.

[radar2000]

Hi Aladin.

THANK YOU!!!

This seemed to do the trick!

To answer your question, I needed this data to be spread accross the spreadsheet, so that I can send out an email merge to dealership owners.

By doing this, I will only need to send one email per dealer which contains their rep information. If I had done it the otherway, I would be sending multiple emails to the same dealership owner for each rep information.

I do various email merges by using Word and Excel.

Yogi, I had tried using the pivot option, but there were too many variables to work with.

Aladin, I've I needed to insert another 2 columns, how would I need to change your formula? For example, I needed to include additional data for the reps in columns D & E.

I would like to thank everyone for their help and knowledge with this problem !