Desperately need a simple "value return" formula

[ArPharazon]

I've been around and around with IF, MATCH, COUNTIF and some other options with no luck outside of a lot of #SPILL errors.

I have 13 "Network ##" values, 1-13 in any of 10 columns in any given row. The CMMS system I use doesn't leave them in a single column when exporting.

I want to return whatever the value is (ex: "Network 1") into a blank cell in a new column, then drag that down so each unique value is in the single column.

Please help. This is driving me bananas right now.

 

[JamesCanale]

I'm not sure if I understand just what you want, but:

MrExcelPlayground19.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1												Silly Way for older excel		Excel 2016
2	Network 1											Network 1		Network 1
3			Network 2									Network 2		Network 2
4		Network 5										Network 5		Network 5
5						Network 9						Network 9		Network 9
6								Network 3				Network 3		Network 3
7			Network 8									Network 8		Network 8
8		Network 7										Network 7		Network 7
9										Network 4		Network 4		Network 4
10								Network 10				Network 10		Network 10
Sheet16

Cell Formulas
Range	Formula
M2:M10	M2	=B2&C2&D2&E2&F2&G2&H2&I2&J2&K2
O2:O10	O2	=CONCAT(B2:K2)

 

[ArPharazon]

I'm not sure if I understand just what you want, but:

MrExcelPlayground19.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1												Silly Way for older excel		Excel 2016
2	Network 1											Network 1		Network 1
3			Network 2									Network 2		Network 2
4		Network 5										Network 5		Network 5
5						Network 9						Network 9		Network 9
6								Network 3				Network 3		Network 3
7			Network 8									Network 8		Network 8
8		Network 7										Network 7		Network 7
9										Network 4		Network 4		Network 4
10								Network 10				Network 10		Network 10
Sheet16

Cell Formulas
Range	Formula
M2:M10	M2	=B2&C2&D2&E2&F2&G2&H2&I2&J2&K2
O2:O10	O2	=CONCAT(B2:K2)

I should have added that wherever the value exists, the cells before it are populated. I am looking at CI3:CR3 in the example and hoping to return the value to CS3.

With some of the other formulas, I was getting a "4" (4th cell in the first row) or "Yes". But whenever I tried a formula I thought would return the value, no luck.

 

[ArPharazon]

An Excel savvy co-worker sent me a shorter version of this formula (5 iterations) that I expanded that works. It's fairly complicated and I'd still love to find a simpler formula that's able to use the range CI3:CR3. I'm not even sure what the repeating "7" represents.

=IF(LEFT($CI3,7)="Network",CI3,(IF(LEFT($CJ3,7)="Network",CJ3,(IF(LEFT($CK3,7)="Network",CK3,(IF(LEFT($CL3,7)="Network",CL3,(IF(LEFT($CM3,7)="Network",CM3,(IF(LEFT($CN3,7)="Network",CN3,(IF(LEFT($CO3,7)="Network",CO3,(IF(LEFT($CP3,7)="Network",CP3,(IF(LEFT($CQ3,7)="Network",CQ3,(IF(LEFT($CR3,7)="Network",CR3,"Not Found")))))))))))))))))))

 

[JamesCanale]

This makes it much easier to do!

MrExcelPlayground19.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M
2	Network 1											Network 1
3	Silly	Goose	Network 2									Network 2
4	Silly	Network 5										Network 5
5	Silly	Goose	Silly	Goose	Silly	Network 9						Network 9
6	Silly	Goose	Silly	Goose	Silly	Goose	Silly	Network 3				Network 3
7	Goose	Silly	Silly									not found
8	Silly	Network 7										Network 7
9	Goose	Silly	Goose	Silly	Goose	Silly	Goose	Goose	Silly	Network 4		Network 4
10	Goose	Silly	Goose	Silly	Goose	Goose	Silly	Network 10				Network 10
Sheet14

Cell Formulas
Range	Formula
M2:M10	M2	=IF(LEFT(INDEX(B2:K2,1,COUNTA(B2:K2)),7)="Network",INDEX(B2:K2,1,COUNTA(B2:K2)),"not found")

The "7" in the other formula takes the 7 left characters of the cell - and sees if they are "Network". If this works, you'll be Golden.

This formula counts the number of items in the row and takes that last one. It will break if there are any blanks before "Network" or anything after "Network" that you don't want to return.

 

[Joe4]

For a row, is it possible to have something like this (where a non "Network" value appears AFTER a "Network" value?
If so, what should be returned in this case?

Or if a Network value does appear in a row, will it always be in the last populated cell in columns CI-CR?

 

[ArPharazon]

This makes it much easier to do!

MrExcelPlayground19.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M
2	Network 1											Network 1
3	Silly	Goose	Network 2									Network 2
4	Silly	Network 5										Network 5
5	Silly	Goose	Silly	Goose	Silly	Network 9						Network 9
6	Silly	Goose	Silly	Goose	Silly	Goose	Silly	Network 3				Network 3
7	Goose	Silly	Silly									not found
8	Silly	Network 7										Network 7
9	Goose	Silly	Goose	Silly	Goose	Silly	Goose	Goose	Silly	Network 4		Network 4
10	Goose	Silly	Goose	Silly	Goose	Goose	Silly	Network 10				Network 10
Sheet14

Cell Formulas
Range	Formula
M2:M10	M2	=IF(LEFT(INDEX(B2:K2,1,COUNTA(B2:K2)),7)="Network",INDEX(B2:K2,1,COUNTA(B2:K2)),"not found")

The "7" in the other formula takes the 7 left characters of the cell - and sees if they are "Network". If this works, you'll be Golden.

This formula counts the number of items in the row and takes that last one. It will break if there are any blanks before "Network" or anything after "Network" that you don't want to return.

This worked well, thanks! It only #SPILLs when there is nothing within the selected range.

ex: =IF(LEFT(INDEX(CI17:CR17,1,COUNTA(CI17:CR17)),7)="Network",INDEX(CI17:CR17,1,COUNTA(CI17:CR17)),"not found")

For a row, is it possible to have something like this (where a non "Network" value appears AFTER a "Network" value?
If so, what should be returned in this case?

View attachment 96455
Or if a Network value does appear in a row, will it always be in the last populated cell in columns CI-CR?

It's a weird system. No matter where I might put a given value in the selected range of cells (portfolios in this case), the next time I export, it's back in whatever order the system wants.

I thought it was alphabetical looking at other exports, but that went out the window with this list. It is alphabetical within the actual Portfolio section of each record.