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!

 

[foxup]

skip,

your formula didn;t work.

makitso,

didn;t work for me.

gavona,

worked like a charm!


skip/ makitso,

you want me to try anything else?


let me know.

thanks,
FOXUP

 

[SkipVought]

Please post EXACTLY what you have in the first 4 rows.

Skip,

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

 

[Gavona]

I missed a few posts as I developed my solution.

Skip, I haven't tried your solution yet. I was challenging myself for a solution not needing a pivot table but would agree that one formula MAY be better than several. Probably the best is whichever the OP can best understand.



Gavin

 

[foxup]

skip,

the first 4 rows of which worksheet? Of worksheet #3, I have this:

4 4 4 4 4 3 3 3 3 3
1 2 3 4 5 1 2 3 4 5
ScottsID name1 name2 name3 name4 name5 title1 title2 title3 title4 title5
 18851974

anything else you wish me to post?


Thanks,
FOXUP

 

[MakeItSo]

foxup

1) Whatever works for you is fine!
If gavonas solution gives you the desired output that's fine.

but:
2) TekTips is chiefly for learning from each other and helping each other. I'd hate to see this chance to test Skips "Formula One" approach zoom by unseen (pun intended).
I tested it and after adapting the formula to German Excel, it worked.
BTW: the French equivalent for "isna" is "estna" indeed.
So Skips formula in French for cell B4 would be

=SI(ESTNA(EQUIV($A4;ScottsID;0));"";INDEX(DECALER(ScottsID;EQUIV($A4;ScottsID;0)-1;B$1;NB.SI(ScottsID;$A4);1);B$2))

“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.

 

[Gavona]

Hey Skip: I nearly gave N1ghteyes a star for his post

[begin SkipVought translation service]

Gavin

 

[MakeItSo]

Have you named your entire ScottsID range A1:A14 as ScottsID on sheet 1?

“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]

Did you NAME your ranges?

You should be able to open the [highlight]Name Box[/highlight] and see a list of your Named Ranges.

Skip,

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

 

[N1GHTEYES]

Hey Gavona: I almost thank you for your comment

 

[foxup]

i did name my ranges and I'll stick with Gavona's solution. Simple & to the point.

Thank you Gavona! Star for you!


Thanks,
FOXUP!

 

[MakeItSo]

And I could have saved me the effort to find the localised function enquivalents and could have instead just used faq68-5422 :

From English to my language

Sub Translate_function_from_English()

Selection.Formula = Selection.Formula

End Sub

Write the function in a cell as you would have written it in the English version (Ignore the error message). Select that cell and run the macro above. This automatically converts the non-working English function in the cell to a working local one, if there is a function within the cell. Otherwise, nothing happends.

Note that the sub works an a multiple selection (array) as well!

Kudos to 01Patrik for this simple but effective little code.



“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]

@Skip:

I still bet your solution "functions" better and faster on the entire 2000 columns.

==>*

“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.

 

[Gavona]

@MakItSo:
I wouldn't be so sure about that. Skip's has a formula with 2 exact Match functions in each of 8 columns on every row.
Mine uses 2 helper columns with CountIf but otherwise the formulae reference precise cells - so I would guess would be quicker.
(I could have made the formula for Name and Title the same with an extra row to refer to as Skip did.]

Gavin

 

[lionelhill]

it still hurts converting a normalised sensible table layout into something very un-normalised. But reading this has been a learning experience. Thanks to all.