Smart questions
Smart answers
Smart people
Join Tek-Tips Forums
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

foxup (Programmer) (OP)
13 Feb 12 16:18
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 (Programmer)
14 Feb 12 8:45
Just create a pivot table.
SkipVought (Programmer)
14 Feb 12 9:11


@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

                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

The formula

B4: =IFERROR(INDEX(OFFSET(ScottsID,MATCH($A4,ScottsID,0)-1,B$1,COUNTIF(ScottsID,$A4),1),B$2),"")



 

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

foxup (Programmer) (OP)
14 Feb 12 9:25
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 (Programmer)
14 Feb 12 9:42


Helllllooooo........

????????

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

N1GHTEYES (TechnicalUser)
14 Feb 12 10:27
[begin SkipVought translation service]

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

[end SkipVought translation service]

Tony
foxup (Programmer) (OP)
14 Feb 12 11:51
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 (Programmer)
14 Feb 12 11:59


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,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

foxup (Programmer) (OP)
14 Feb 12 12:05
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 (Programmer) (OP)
14 Feb 12 12:11
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
 
Helpful Member!  SkipVought (Programmer)
14 Feb 12 12:11

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???

B4: =if(isna(MATCH($A4,ScottsID,0)),"",INDEX(OFFSET(ScottsID,MATCH($A4,ScottsID,0)-1,B$1,COUNTIF(ScottsID,$A4),1),B$2))

 

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

foxup (Programmer) (OP)
14 Feb 12 12:18
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 (TechnicalUser)
14 Feb 12 12:20
Here is a solution.  To try it first copy this into a new worksheet at cell A1.
Then use Data, text2Columns comma delimited.

CODE

,,,,,,,,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 (Programmer)
14 Feb 12 12:22


Quote:

in sheet3 I have headers only
Did you read my instructions?

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

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

MakeItSo (Programmer)
14 Feb 12 12:30
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 (Programmer) (OP)
14 Feb 12 12:33
Gavona,

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


Thank You,
FOXUP
MakeItSo (Programmer)
14 Feb 12 12:34
Here's a slightly adapted version - although still incomplete as I don't know the French equivalent for "isna".


=SI(ISNA(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 (Programmer)
14 Feb 12 12:36
The English formulae worked for you? That's fine then.
Nice work, Skip!
thumbsup2

"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 (Programmer)
14 Feb 12 12:40

@gavona

is ONE FORMULA better?

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

foxup (Programmer) (OP)
14 Feb 12 12:41
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
foxup (Programmer) (OP)
14 Feb 12 12:43
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 (Programmer)
14 Feb 12 12:43

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

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

Gavona (TechnicalUser)
14 Feb 12 12:50
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 (Programmer) (OP)
14 Feb 12 12:51
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 (Programmer)
14 Feb 12 12:51
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

CODE

=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 (TechnicalUser)
14 Feb 12 12:53
Hey Skip: I nearly gave N1ghteyes a star for his post

Quote:

[begin SkipVought translation service]
wink

Gavin

MakeItSo (Programmer)
14 Feb 12 12:53
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 (Programmer)
14 Feb 12 12:53

Did you NAME your ranges?

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

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

N1GHTEYES (TechnicalUser)
14 Feb 12 13:01
Hey Gavona: I almost thank you for your comment :)
foxup (Programmer) (OP)
14 Feb 12 13:04
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 (Programmer)
14 Feb 12 13:05
And I could have saved me the effort to find the localised function enquivalents and could have instead just used FAQ68-5422: How to translate functions :

Quote:

From English to my language

CODE

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

"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 (Programmer)
14 Feb 12 13:09
@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.

Helpful Member!  Gavona (TechnicalUser)
14 Feb 12 14:12
@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 (TechnicalUser)
15 Feb 12 14:26
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.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close