• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Pivot Table and sequence

A

Ateeb Ali

Member
Dear Sir
Please find attached file, I need help like Sheet1 shows Sequence and Approver in Vertical form but I need in horizontal form so report can be viewed on single page.

like user 8 in d2, i want sequence 1 2 3 4 in columns g h i j
and approver user name in like g2, h2, i2, j2
all blank space in middle will be removed and report will be short, I want help in this.




Regards
Ateeb Ali
 

Attachments

Ateeb Ali said:
Dear Sir
Please find attached file, I need help like Sheet1 shows Sequence and Approver in Vertical form but I need in horizontal form so report can be viewed on single page.

like user 8 in d2, i want sequence 1 2 3 4 in columns g h i j
and approver user name in like g2, h2, i2, j2
all blank space in middle will be removed and report will be short, I want help in this.




Regards
Ateeb Ali
Click to expand...
Hi Ateeb, are you aggregating anything with this pivot? It's kinda what a pivot is meant to do.
You can drag the sequence and approver from rows to the columns. But you need something in values too, to make some sense with the table.
I'm not sure that a pivot is the best option for what you want to accomplish. I've made a Power Query solution as alternative. Do you have that option in your Excel? (for windows Excel, PQ is available as from version 2010).
 

Attachments

Dear Sir
Perfect, actually I use office PC and installed windows xp with Microsoft office version 2010.

I just checked and Power Query solution is not available for XP.

Is there any way to do it, I also want to learn it.

Noted that it can not be do through pivot.
 
Ateeb Ali said:
Dear Sir
Perfect, actually I use office PC and installed windows xp with Microsoft office version 2010.

I just checked and Power Query solution is not available for XP.

Is there any way to do it, I also want to learn it.

Noted that it can not be do through pivot.
Click to expand...
Oh my, still using XP... MS has abandoned support for that one some years back, I assumed it is not longer used in the professional world.
Unfortunately, if your pc does not meet minimum requirements, sorry to say that PQ is not (yet) for you then.
Sounds like you'd need a VBA script then. But that is not really my cup of thee. I mean, I'm like a DIY'er (make it work for me), whereas the ninja's here I truly professionals in that domain (make it work for you).
 
Sir, it will be extracted from erp system reports
see attached actual report
I want sequence to come on top and after that top heading will be approver status then all names and date in vertical table
 

Attachments

Ateeb Ali
Would You make the ideal layout of Your needed output?
... or ...
do I add that date to below of Approver in same cell?

and
It would be 'easer' for You, if 'sample' and 'real' files would have same layout!
 
Formula solution,

1] In A2, formula copied down :

=IFERROR(INDEX(SEL_Purchase_Requisition_Statu_!A$6:A$319,MATCH(0,INDEX(COUNTIF(A$1:A1,SEL_Purchase_Requisition_Statu_!A$6:A$319),0),0)),"")

2] In B2, formula copied across to I2 and all copied down :

=IF($A2="","",VLOOKUP($A2,SEL_Purchase_Requisition_Statu_!$A$6:$I$319,COLUMNS($A:B),0))

3] In J2, formula copied across to P2 and all copied down :

=IF($A2="","",IF(COLUMNS($A:A)<=COUNTIF(SEL_Purchase_Requisition_Statu_!$A$6:$A$319,$A2),OFFSET(SEL_Purchase_Requisition_Statu_!$K$5,MATCH($A2,SEL_Purchase_Requisition_Statu_!$A$6:$A$319,0)+J$1-1,0),""))

4] See attachment

Regards
Bosco
 

Attachments

You must log in or register to reply here.
Back
Top