Hi Guys,
I have a list of names in collum A from A2 to A157 with blank cells inbetween some of the names. I want to create a new list without the blanks. The data in collum A is changing constantly show the NEW list should be changing as well.
Thank you guys.
Do you need the new list in column A, or in a seperate location e.g. in Column B, sheet 2 etc
I need it in a new sheet. so separate location
in a new sheet starting at b11
I have 157 cells that are always changing but the company list will only have 20 cells max with data at any given time
Let's assume the data is on Sheet1 in the range A2:A157.Originally Posted by jphilipson
On some other sheet enter this array formula** in A2:
=IFERROR(INDEX(Sheet1!A:A,SMALL(IF(Sheet1!A$2:A$157<>"",ROW(Sheet1!A$2:A$157)),ROWS(A$2:A2))),"")
** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
Copy down until you get blanks.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Ok, array entered** in B11:
=IFERROR(INDEX(Sheet1!A:A,SMALL(IF(Sheet1!A$2:A$157<>"",ROW(Sheet1!A$2:A$157)),ROWS(B$11:B11))),"")
** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
Copy down until you get blanks.
that worked, THANK YOU!
You're welcome. Thanks for the feedback!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks