Return value of blank cells in a row

[Lauren_M]

Hey everyone,

Since you were all so helpful yesterday, I thought I'd come back again today and see if you are able to assist with my next issue.
I have exported a customer database from our system and each of our customers have certain "roles" applied to them depending on what they have purchased. Some customers might have 3 roles and other customers may have 10 roles. They all export in the one cell and the roles are seperated by a ;
I use a text to column function to get each of the role into it's own row, I then use a filter function and clear the contents of the cells I don't require for this particular report.
So I am left with over 3,000 customers and their role could be anywhere in row 1 through to row 10 and I need to get them all into the one row so that I can remove roles that have expired.

So I was hoping there might be a formula to return the first non-blank value in a row range.

Perhaps I am tackling this the whole wrong way and am totally open to any suggestions.

Hopefully I have provided enough information, please let me know if I have not.

As always, I appreciate all answers/advice/help.

Regards,

Lauren

 

[sheetspread]

Excel 2010
	A	B	C	D	E	F	G	H	I	J
1	Red		Red
2	Orange					Orange
3	Yellow				Yellow
4	Green			Green
5	Blue			Blue
6	Indigo	Indigo
7	Violet									Violet
8	Silver								Silver
9	White				White
10	Clear		Clear
Sheet5

Cell Formulas
Range	Formula
A1	=INDEX(B1:K1,SUMPRODUCT(--(B1:K1<>""),COLUMN(B1:K1))-1)

 

[sheetspread]

Use this to retrieve the first of multiple row entries:

http://www.mrexcel.com/forum/excel-...rance-multiple-columns-put-single-column.html

 

[Lauren_M]

Thanks a bunch! This returns a "0" value in my spread sheet, however when I replicated yours above it worked! So very strange and I have to keep playing around with mine...

 

[sheetspread]

Try changing the format and checking for named ranges (common problems with database imports)

 

[Lauren_M]

I've worked out why, it's because there is multiple values in row. There is the role, then the start date and the finish date. I added another colour into your spread sheet and it returned the "0" value.
So I deleted the start date and end date, but it still doesn't work.
Do I need to use the "if" statement since I have more than one cell with data in a row?

 

[sheetspread]

Try:

Excel 2010
	A	B	C	D	E	F	G	H	I	J
1	Red		Red		Maroon
2	Gray	Gray				Orange
3	Yellow				Yellow
4	Green			Green
5	Blue			Blue
6	Indigo	Indigo
7	Turquoise				Turquoise					Violet
8	Silver								Silver
9	White				White
10	Clear		Clear
Sheet5

Cell Formulas
Range	Formula
A1	=INDEX(B1:K1,MATCH(TRUE,INDEX((B1:K1<>0),0),0))

 

[Lauren_M]

That worked!!!!! yay Thanks a bunch! Now if I wanted to return the second value in that row I have tried changing the last 0 to -1, is that right? Or is there a different way?

 

[sheetspread]

Excel 2010
	A	B	C	D	E	F	G	H	I	J
1	Maroon		Red		Maroon
2	Orange	Gray				Orange
3	Yellow		BBB		Yellow
4	TTT			Green	TTT
5	RRR			Blue		RRR
6	JJJ	Indigo	JJJ
7	Violet				Turquoise					Violet
8	Silver		SSS						Silver
9	QQQ				White				QQQ
10	VVV		Clear				VVV
Sheet5

Cell Formulas
Range	Formula
A1	{=INDEX(B1:K1,SMALL(IF(ISBLANK(B1:K1),"",COLUMN(B1:K1)),2)-COLUMN(B1:K1)+1)}
Press CTRL+SHIFT+ENTER to enter array formulas.

Change the 2 to a 3, etc. It will return an error if there are fewer than n nonblank cells.

 

[Lauren_M]

Wow! Thanks so much for your help! I really really appreciate it!