How do I condense values in a column so that there are no empty cells between values in the same column and without losing the order of the values (as one does when using the sort from largest to smallest value function).
See attached file.
Many thanks,
JackBlack2
Hi,
in D2 to be copied down
=IFERROR(INDEX(B$1:B$100,AGGREGATE(15,6,ROW($1:$100)/(B$1:B$100<>""),ROWS($1:1))),"")
Regards
Last edited by canapone; 04-23-2019 at 02:58 PM.
-----------------------------------------------------
At Excelforum, you can say "Thank you!" by clicking the star icon ("Add Reputation") below the post.
Please, mark your thread [SOLVED] if you received your answer.
Hi again
another approach in D2
=IFERROR(INDEX(B$2:B$100,MATCH(0,INDEX(COUNTIF(D$1:D1,$B$2:$B$100&""),),0)),"")
Regards
If you add a header to the column, you can use AutoFilter. Filter on blank entries, select the entire rows and delete them. Done.
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
@ canapone
I have a problem - the example I gave does not correspond to the columns in my file. So I had to change the formula but found it gives a circular reference error. So could you please modify the second formula you wrote out so that column B in the attached file refers to column I and column D in the attached file refers to column J.
Geez, how is it that the geeks at Microsoft have a button for every complex function but for the most simple functions like condensing data there is no button to click??
Hi,
not sure to have understood: the red segment probably needs more attention when adjusted.
In J2
=IFERROR(INDEX(D$2:D$100,MATCH(0,INDEX(COUNTIF(J$1:J1,$D$2:$D$100&""),),0)),"")
Regards
Why not filter your data (filter out the blanks), then copy and paste to a new destination?
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.
@canapone
I just updated the spreadsheet to show you exactly what I meant. Should have known to show the example exactly as it appears in my file.
Thanks and sorry,
JackBlack
Hi (no problem)
numbers to be condensed in i2:i100
In orange J2
=IFERROR(INDEX(I$2:I$100,MATCH(0,INDEX(COUNTIF(J$1:J1,I$2:I$100&""),),0)),"")
to be copied down
In yellow K2 same solid list from
=IFERROR(INDEX(I$1:I$100,AGGREGATE(15,6,ROW($1:$100)/(I$1:I$100<>""),ROWS($1:1))),"")
Regards
Last edited by canapone; 04-24-2019 at 02:38 PM.
@canapone
I should have mentioned that I need the formula to condense values all the way down to line 20,000, not just a hundred lines down. Again a tried to modify the formula but it does not return the right values. Sorry again.
@AliGW
Could you inform the geeks at Microsoft to add this most simplest of functions into the function bar? I have spent way too much time on something that should be a basic sort function offered by Excel.
Hi
in terms of efficiency the segment ...COUNTIF(J$1:J1,I$2:I$100&"")... in the first formula is very poor: maybe AGGREGATE.
=IFERROR(INDEX(I$2:I$20000,AGGREGATE(15,6,ROW($2:$20000)-1/(I$2:I$20000<>""),ROW(A1))),"")
or if you don't have to list any zero
=IFERROR(INDEX(I$2:I$20000,AGGREGATE(15,6,ROW($2:$20000)-1/I$2:I$20000,ROW(A1))),"")
Regards
What makes you think I should do it for you? Anyone can add to the MS User Voice facility, so do it yourself.Originally Posted by jackblack2
@AliGW
So glad you asked that question. Sorry if I got your back up.
None of the big tech corporations provide any human tech support despite rolling in hundreds of billions of dollars (Microsoft is the third company on the verge of becoming a trillion dollar company). They say its impossible. I call that bull. They just don't want to fork out on salaries. They would rather have artificial intelligence answer questions or have forum moderators who volunteer (correct me if I am wrong).
So I cant know if a post I make will ever get to an actual Microsoft employee without it being filtered out by some artificial intelligence algorithm. Hence I asked you and not artificial intelligence if you could forward my feedback. I also asked you because I would assume as you are a forum moderator, Microsoft asks for your feedback every now and again.
@canapone
Thanks. Problem solved. Still think that Excel should have a function in the function bar which does this simple action.
Jackblack2 - this forum is not run by Microsoft or for Microsoft. None of those who volunteer their help here are employed by Microsoft as far as I am aware. In terms of my role as moderator, I was invited to join the moderation team here. What I and others do here is unpaid and in our free time. This forum is, I repeat, completely independent of Microsoft. In my real life, I am a teacher and head of a large languages department in a school in the UK.
I hope this clears up the very strange misconceptions you appear to be harbouring about this place and about myself and others who help here.
@AliGW
Thanks for clearing up those very strange misconceptions I had. I have to say that as a forum completely independant of Microsoft and completely voluntary, you all do a fantastic job.
Kind of you to say so and thanks.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks