Consolidation of Data In A Column

raymaster98

Board Regular
Using Excel 2003.

Please forgive me, but I have made a similar post before on this topic, but I cannot seem to edit the formula to make it work today. In rows 3-5000 of column BP, I have formulas that return a list of patient names from another data source. There are many blank cells between each name. In cell CD3, I am looking for a formula to return the first non blank value (patient name) in that column. Then, I need to drag the formula down so that cell CD4 will return the second non blank cell (patient name) and so on. BTW, I need the solution to be automated (via formula) instead of a filter to "skip blanks". If it helps, I have pasted the formula that worked before on another workbook. Thanks, Kenny

=IF(INDEX(\$Z\$2:\$Z\$10000,MATCH(<st1:stockticker w:st="on">TRUE</st1:stockticker>,\$Z\$2:\$Z\$10000<>"",0))=AM\$2,IF(ROWS(AP\$2:AP2)<=COUNTIF(\$AB\$2:\$AB\$10000,"<>"),INDEX(\$AB\$2:\$AB\$10000,SMALL(IF(\$AB\$2:\$AB\$10000<>"",<st1:stockticker w:st="on">ROW</st1:stockticker>(\$AB\$2:\$AB\$10000)-<st1:stockticker w:st="on">ROW</st1:stockticker>(\$AB\$2)+1) ,ROWS(AP\$2:AP2))),""))
with curly braces on each end

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Using Excel 2003.

Please forgive me, but I have made a similar post before on this topic, but I cannot seem to edit the formula to make it work today. In rows 3-5000 of column BP, I have formulas that return a list of patient names from another data source. There are many blank cells between each name. In cell CD3, I am looking for a formula to return the first non blank value (patient name) in that column. Then, I need to drag the formula down so that cell CD4 will return the second non blank cell (patient name) and so on. BTW, I need the solution to be automated (via formula) instead of a filter to "skip blanks". If it helps, I have pasted the formula that worked before on another workbook. Thanks, Kenny

=IF(INDEX(\$Z\$2:\$Z\$10000,MATCH(<st1:stockticker w:st="on">TRUE</st1:stockticker>,\$Z\$2:\$Z\$10000<>"",0))=AM\$2,IF(ROWS(AP\$2:AP2)<=COUNTIF(\$AB\$2:\$AB\$10000,"<>"),INDEX(\$AB\$2:\$AB\$10000,SMALL(IF(\$AB\$2:\$AB\$10000<>"",<st1:stockticker w:st="on">ROW</st1:stockticker>(\$AB\$2:\$AB\$10000)-<st1:stockticker w:st="on">ROW</st1:stockticker>(\$AB\$2)+1) ,ROWS(AP\$2:AP2))),""))
with curly braces on each end
Try this...

Book1
ABCD
1Name_CountName
2Tom_9Tom
3___Sue
4Sue__Eric
5Eric__Joe
6Joe__Lisa
7Lisa__Pat
8___Kristy
9Pat__Pam
10___Biff
11Kristy___
12____
13Pam___
14____
15Biff___
Sheet1

Enter this formula in C2. This will return the count of records.

=COUNTIF(A2:A15,"?*")

Enter this array formula** in D2.

=IF(ROWS(D\$2:D2)>C\$2,"",INDEX(A:A,SMALL(IF(A\$2:A\$15<>"",ROW(A\$2:A\$15)),ROWS(D\$2:D2))))

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

Perfect solution T. Valko. Works like a charm. Thank you very much

Excel 2007

copy collumn A to D.

Data => remove duplicates

You get the same list.

Perfect solution T. Valko. Works like a charm. Thank you very much
You're welcome. Thanks for the feedback!

Replies
0
Views
102
Replies
9
Views
266
Replies
6
Views
194
Replies
3
Views
188
Replies
3
Views
247

1,203,075
Messages
6,053,392
Members
444,661
Latest member
liamoohay

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back