Consolidation of Data In A Column

[raymaster98]

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

 

[T. Valko]

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
	A	B	C	D
1	Name	_	Count	Name
2	Tom	_	9	Tom
3	_	_	_	Sue
4	Sue	_	_	Eric
5	Eric	_	_	Joe
6	Joe	_	_	Lisa
7	Lisa	_	_	Pat
8	_	_	_	Kristy
9	Pat	_	_	Pam
10	_	_	_	Biff
11	Kristy	_	_	_
12	_	_	_	_
13	Pam	_	_	_
14	_	_	_	_
15	Biff	_	_	_
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.

 

[raymaster98]

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

 

[Oeldere]

Excel 2007

copy collumn A to D.

Data => remove duplicates

You get the same list.

 

[T. Valko]

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

You're welcome. Thanks for the feedback!