I have Horizontal Data in C2:G2
I want this data to be listed Vertical and consecutive at I2 but with one condition
Example :
C2 is listed because there is data in all C4:C9
D2 is not listed because D4:D9 has missing data
E2 is listed because there is data in all E4:E9
...
Straight to the point, as usual - no pleasantries, no "please" in sight. I guess you don't mean to be so abrupt ...
Anyway, I'd probably do this with PowerQuery, and if you are interested and nobody else gets in first, I'll have a look in the morning, but I'm on the iPad now, so can't access PQ.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Ohhh, I didn't know you are on line !!!
Not at all, I try to be as simple as possible.
But I always thank and leave positive feedback !
Yes, but you know, it would be so nice if you just said "thank you in anticipation" so that it didn't feel so much like a takeaway order. We are a happy community here - it's nice if people treat it as such, especially those who come here often.
Would you be interested in PQ for this, or is it off limits for you?
Sorry again, no one need to "execute" orders here in their own time !
I would like a formula !
Okey dokey. You don't know what you're missing ...
I expect someone will have something for you very soon.
c12 =counta(C4:C9) and drag to the right
c13 =If(C12<>6,"",countif($C$12:C$12,6)) and drag to the right.
K2 =Iferror(INDEX($C$2:$G$13,1,Match(row()-1,$C$13:$G$13,0)),"") and drag down
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
This is working fine !, thank you...
I want to see if someone can come up wit an ideea without help rows/columns
In my real work, I have dynamic data from C2 to ....250 columns
So it is difficult to add help, but I can do that too
Last edited by ionelz; 05-29-2020 at 01:53 PM.
In that case, I would suggest you take a look at the solutions AliGW offered.
Please try at I2
=IFERROR(INDEX($C$2:$G$2,AGGREGATE(15,6,COLUMN($C$4:$G$9)/(MMULT(TRANSPOSE(ROW($C$4:$G$9)^0),--($C$4:$G$9=""))=0),ROWS(I$2:I2))-COLUMN($C$2)+1),"")
or Open Power Query/Get and Transform. Click on New Query.
Open blank query in the editor, launch Advanced Editor and paste in the following code.
Please Login or Register to view this content.
With the code below to re-arange the data.
After that a table with a helpcolumn.
After that a pivot table.
See the attached file.
Please Login or Register to view this content.
Thank you all, problem solved !
I would like also to thank you for spending time to write a VBA for this, I have tested them
Thanks
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks