Nesting Alternative

[Learn2008]

I have 11 columns of data, in which I have 10 zeros and one (1) text answer e.g.

0,0,0,0,0,0,0,0,Service,0

In cell A2 I am trying to have the answer be "Service"


In cell A2 I have the following formula which works fine if the answer "Service" is in colums Y - AF but when trying to nest I am limited to the 8 nest rule. Because I am not summing numbers I am not sure how to work through trying to find isolated text in a row. I have reviewed Lookup functions but with the randomness of the text result I am not sure whether it can be built into a lookup range.

<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=64><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 13.5pt; mso-height-source: userset" height=18><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 13.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" id=td_post_2599216 height=18 width=64><TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=64><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 13.5pt; mso-height-source: userset" height=18><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 13.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" id=td_post_2599216 class=xl65 height=18 width=64>IF(Y13=Y$1,Y$1,IF(Z13=Z$1,Z$1,IF(AA13=AA$1,AA$1,IF(AB13=AB$1,AB$1,IF(AC13=AC$1,AC$1,IF(AD13=AD$1,AD$1,IF(AE13=AE$1,AE$1,IF(AF13=AF$1,AF$1,0))))))))</TD></TR></TBODY></TABLE>
</TD></TR></TBODY></TABLE>

 

[Dryver14]

Ill keep looking anyway just for the project but most of the people on here actually know what they are taIlking about so you will get an answer,

I know I have seen this done

 

[Dryver14]

I knew I would get there in the end,
Just had to write a macro is all.

If you add a column this will work but you will have to add the heading to the select case

Option Explicit
Sub FindValue()
Dim i As Integer
Dim x As Integer
Dim v As Integer
Dim cell As Range
v = 26
Cells(2, v).Resize(20, 1).Select
    For Each cell In Selection
    For i = 2 To 20
        Select Case Cells(i, v)
            Case "5000S", "8000S", "2000G", "3000G", "5000G", "Sliders", "AD", "Service", "Kyi", "Flex", "Other"
                Cells(i, 1) = Cells(i, v).Value
            Case Else
        End Select
    Next
    v = v + 1
    Next
Cells(1, 1).Select
End Sub

 

[West Man]

My previous formula errors. In X13 try: =lookup(True,Y13:AI13<>0,Y1:AI1)

 

[Learn2008]

Hello West Man,

Thank you for your formula. It works for columns AG to AI but not for Columns Y to Af. In these columns it returns the value of AI$1 being "Other".

I combined your formula with the 7 nests by adding a new column, AJ11

Amount in X11 was 0, because all values Y11:AF11

Value in AG11 was "Flex"

Formula used

=IF(X11=0,LOOKUP(TRUE,AG11:AI11<>0,AG$1:AI$1),X11)

Value in AG11 was Flex but answer in

Result was "other" again same as in formula earlier.

For some reason the Lookup defaults to the end lookup cell.

But again, another step closer thanks to your assistance.

I will try Dryver14's macro option next.

Please accept my appreciation for your efforts.

 

[Learn2008]

Dryver14 = Success!!

Great script.

Just one minor query, I generally run a lot of macros using SelectEnd and offsets to define data areas because they vary every time we download information, how can I incorporate a simple row count in your script.

E.g. the model at the moment has 180 rows (your script has 20 rows) which I amended to 180 but tomorrow I will have for example 215.

I generally used defined areas and names for data ranges,can you assist me with either a count function to automatically calculate and the number of rows in the macro or how to embed a defined range within your script. I have not used the looping function that often.

Again to Dryver14 and West Man thanks heaps for solving this vexing issue for me. I am now able to move to the next stage which is using the new data for Sumifs calculations.

 

[West Man]

Try this as an alternative (it has been tested) =LOOKUP(REPT("z",255),Y13:AI13)

 

[tushiroda]

you can do it with an array formula. rememer - control + shift + enter

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
1	Product Group	5000S	8000S	2000G	3000G	5000G	Sliders	AD	Service	Kyi	Flex	Other
2	Service	0	0	0	0	0	0	0	Service	0	0	0
3	2000G	0	0	2000G	0	0	0	0	0	0	0	0
4	5000S	5000S	0	0	0	0	0	0	0	0	0	0
5	0
6	0
7	Flex		0	0	0	0	0	0	0	0	Flex	0
8	0
9	0
10	5000G	0	0	0	0	5000G	0	0	0	0	0	0
11	0
12	0
13	Other		0	0	0	0	0	0	0	0	0	Other
Sheet3

Excel 2007

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

 

[Learn2008]

West Man, What can I say - excellent option. I have tested it and it works on my file.

Your option allows me to automate my macro and have this formula inserted as needed without manual interception.

Today has started great, I am on the road again to completing this task.

Tushiroda, thank you for the alternative suggested.

With this solution "Nesting Alternative" has been resolved, thank you to all that have assisted.