How to get this result 2

[foxup]

Hi,

I have an Excel workbook (contains about 2000 rows). Here is a sample:

ScottsID FirstName LastName Title fullname
18851974 Alan Gray Prés Alan Gray
18851974 Pam Robinson Dir de compte/Dir des opér ajd Pam Robinson
18851974 Jill Haworth Dir opér Jill Haworth
18851974 Karl Atkins Rep ventes Karl Atkins
18392556 Tom Toonen Prés Tom Toonen
18392556 Marlène Roy Contrôleuse Marlène Roy
19625675 Nathan Cohen Prop Nathan Cohen
18973025 Chantal Thériault Sec Chantal Thériault
84384529 Lou Lapointe Prop Lou Lapointe
25796428 Michael O'Leary VP Michael O'Leary
25796428 Vanessa Radu Coord mktg Vanessa Radu
19968257 Bruce Bott Prés Bruce Bott
19968257 Vilem Kostlévy Dir gén Vilem Kostlévy


and this is what I want to obtain:

ScottsID Name 1 Name 2 Name 3 Name 4 Name 5 Title 1 Title 2 Title 3 Title 4 Title 5
18851974 Alan Gray Pam Robinson Jill Haworth Karl Atkins Prés Dir de compte/Dir des opér ajd Dir opér Rep ventes
18392556 Tom Toonen Prés
19625675 Nathan Cohen Prop
18973025 Chantal Thériault Sec
84384529 Lou Lapointe Prop
25796428 Michael O'Leary Vanessa Radu VP Coord mktg
19968257 Vilem Kostlévy Dir gén



I really need some help.

Please let me know how.


Thanks,
FOXUP!

 

[Gruuuu]

Just create a pivot table.

 

[SkipVought]

@Gruuuu Sorry, a PivotTable will not give the OP what he wants. A PT is a numerical aggregation tool.

This is an abnormal structure that Excel is not equiped to produce, since it has absolutely no function advantage that works with spreadsheet features.

1. You can use a PT to produce a unique list of ScottsID and the count to rows for each ScottsID.

2. Knowing this, you can code a formula to form a solution, like, assuming that your list of unique ScottsID is in column A and using NAMED RANGES...

the report table on a new sheet starting in A1
[tt]
4 4 4 4 4 3 3 3 3 3
1 2 3 4 5 1 2 3 4 5
ScottsID Name 1 Name 2 Name 3 Name 4 Name 5 Title 1 Title 2 Title 3 Title 4 Title 5
18851974
18392556
19625675
18973025
84384529
25796428
19968257
[/tt]
The formula
[tt]
B4: [highlight]=IFERROR(INDEX(OFFSET(ScottsID,MATCH($A4,ScottsID,0)-1,B$1,COUNTIF(ScottsID,$A4),1),B$2),"")[/highlight]
[/tt]




Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[foxup]

Hi,

I tried with a Pivot Table & I also tried with Transpose and nothing really seems to work due to the darn data not being really completely consistent (in my opinion).

Would it be possible for somebody to guide me please.


Thanks,
FOXUP

 

[SkipVought]

Helllllooooo........

????????

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[N1GHTEYES]

[begin SkipVought translation service]

"did you try the formula and method I suggested?"

[end SkipVought translation service]

Tony

 

[foxup]

Yes, I did try it. I have my PT giving me this:

ScottsID Count of fullname
18392556 2
18851974 4
18973025 1
18974338 3
19142421 2
19625675 1
19968257 2
25796428 2
84384529 1

Your formula gives an error saying:
#NAME?

Any help please.

Thanks,

 

[SkipVought]

Please explain EXACTLY how your sheet is configured.

I only stated that you can use the PT to produce a unique list of ScottsID. THATS ALL.

Did you carefully read and follow ALL the setup instructions? Including entering your formula in B4?

I did fail to say: copy B4 and then paste across all heading columns and down thru all ScottsID rows.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[foxup]

row 1- sheet 1 has headers:
ScottsID FirstName LastName Title fullname
row 2 downwards has the data I mentionned in the previous post:
18851974 Alan Gray Prés Alan Gray
18851974 Pam Robinson Dir de compte/Dir des opér ajd Pam Robinson
18851974 Jill Haworth Dir opér Jill Haworth
18851974 Karl Atkins Rep ventes Karl Atkins
18392556 Tom Toonen Prés Tom Toonen
18392556 Marlène Roy Contrôleuse Marlène Roy
19625675 Nathan Cohen Prop Nathan Cohen
18973025 Chantal Thériault Sec Chantal Thériault
84384529 Lou Lapointe Prop Lou Lapointe
25796428 Michael O'Leary VP Michael O'Leary
25796428 Vanessa Radu Coord mktg Vanessa Radu
19968257 Bruce Bott Prés Bruce Bott
19968257 Vilem Kostlévy Dir gén Vilem Kostlévy

Yes, I entered it in B4 but the problem is I have Excel 2003. I do believe excel 2003 does not have the IFERROR funtion!


Any help.


Thanks,
FOXUP!

 

[foxup]

Sheet 2 is the pivot table:

ScottsID Count of fullname
18392556 2
18851974 4
18973025 1
18974338 3
19142421 2
19625675 1
19968257 2
25796428 2
84384529 1
Grand Total 18

 

[SkipVought]

Excel 2003. That is a very important piece of information that you failed to serve up, my friend!

Why did it take you this long to state???
[tt]
B4: [highlight]=if(isna(MATCH($A4,ScottsID,0)),"",INDEX(OFFSET(ScottsID,MATCH($A4,ScottsID,0)-1,B$1,COUNTIF(ScottsID,$A4),1),B$2))[/highlight]
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[foxup]

Sorry, I just realized that IFERROR was not available in v2003.

OK, so let me me continue, in sheet3 I have headers only, they are:

ScottsID Name1 Name2 Name3 name4 Name4 Title1 Title2 Title3 Title4 Title5


I put your formula in B4 of sheet 3 and I get nothing. No error, no result.

Anything else please?


Thanks,
FOXUP

 

[Gavona]

Here is a solution. To try it first copy this into a new worksheet at cell A1.
Then use Data, text2Columns comma delimited.

,,,,,,,,1,2,3,4,1,2,3,4
ScottsID,FirstName,LastName,Title,fullname,ID_Count,max,ScottsID,Name1,Name2,Name3,Name4,Title1,Title2,Title3,Title4
18851974,Alan,Gray,Prés,Alan Gray,1,FALSE,,,,,,,,,
18851974,Pam,Robinson,Dir de compte/Dir des opér ajd,Pam Robinson,2,FALSE,,,,,,,,,
18851974,Jill,Haworth,Dir opér,Jill Haworth,3,FALSE,,,,,,,,,
18851974,Karl,Atkins,Rep ventes,Karl Atkins,4,TRUE,18851974,Alan Gray,Pam Robinson,Jill Haworth,Karl Atkins,Prés,Dir de compte/Dir des opér ajd,Dir opér,Rep ventes
18392556,Tom,Toonen,Prés,Tom Toonen,1,FALSE,,,,,,,,,
18392556,Marlène,Roy,Contrôleuse,Marlène Roy,2,TRUE,18392556,Tom Toonen,Marlène Roy,,,Prés,Contrôleuse,,
19625675,Nathan,Cohen,Prop,Nathan Cohen,1,TRUE,19625675,Nathan Cohen,,,,Prop,,,
18973025,Chantal,Thériault,Sec,Chantal Thériault,1,TRUE,18973025,Chantal Thériault,,,,Sec,,,
84384529,Lou,Lapointe,Prop,Lou Lapointe,1,TRUE,84384529,Lou Lapointe,,,,Prop,,,
25796428,Michael,O'Leary,VP,Michael O'Leary,1,FALSE,,,,,,,,,
25796428,Vanessa,Radu,Coord mktg,Vanessa Radu,2,TRUE,25796428,Michael O'Leary,Vanessa Radu,,,VP,Coord mktg,,
19968257,Bruce,Bott,Prés,Bruce Bott,1,FALSE,,,,,,,,,
19968257,Vilem,Kostlévy,Dir gén,Vilem Kostlévy,2,TRUE,19968257,Bruce Bott,Vilem Kostlévy,,,Prés,Dir gén,,

Now in row 3 enter formulae as follows:
In F3 =COUNTIF(A$2:A3,A3)
in G3 =COUNTIF(A3:A$15,A3)=1
in H3 =A3
in I3 =IF(I$1-$F3>0,"",OFFSET($E3,-$F3+I$1,0,1,1))
copy I3 to J3,K3,L3
in M3 =IF(M$1-$F3>0,"",OFFSET($D3,-$F3+M$1,0,1,1))
Copy M3 to N3, O3, P3

Now copy those formulae to all rows:
Copy F3 to P3 to all your data rows

Filter your data so only the rows containing "True" in column G are visible.
Copy and pastespecial values to wherever you want the results





Gavin

 

[SkipVought]

in sheet3 I have headers only

Did you read my instructions?

What about the FIRST TWO ROWS that you failed to account for?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[MakeItSo]

One moment:
I'm checking the French equivalents for these functions Skip.
Localised Excel versions might not understand these. I had to translate them to their German equivalents - and replace the separating commas with semicolons - before I got your formula to work.
Does work fine though.

Be back with more info.


“Knowledge is power. Information is liberating. Education is the premise of progress, in every society, in every family.” (Kofi Annan)
Oppose SOPA, PIPA, ACTA; measures to curb freedom of information under whatever name whatsoever.

 

[foxup]

Gavona,

You got it! Perfect!! Wow! Nice one! What a star!


Thank You,
FOXUP

 

[MakeItSo]

Here's a slightly adapted version - although still incomplete as I don't know the French equivalent for "isna".


=SI([red]ISNA[/red](EQUIV($A4;ScottsID;0));"";INDEX(DECALER(ScottsID;EQUIV($A4;ScottsID;0)-1;B$1;NB.SI(ScottsID;$A4);1);B$2))


Probably "ESTNA" but I'm not sure.

“Knowledge is power. Information is liberating. Education is the premise of progress, in every society, in every family.” (Kofi Annan)
Oppose SOPA, PIPA, ACTA; measures to curb freedom of information under whatever name whatsoever.

 

[MakeItSo]

The English formulae worked for you? That's fine then.
Nice work, Skip!


“Knowledge is power. Information is liberating. Education is the premise of progress, in every society, in every family.” (Kofi Annan)
Oppose SOPA, PIPA, ACTA; measures to curb freedom of information under whatever name whatsoever.

 

[SkipVought]

@gavona

is ONE FORMULA better?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[foxup]

Skipvought,

Even if I put those 2 extra rows you ask me, I still get now result in the B4 CELL.


anything else you want me to try?


Thanks,
FOXUP