Formula to skip a certain value and continue on the sequence

[Fantoi]

Hi everyone!

First time reaching out for help, I have a sequence in column A and would like to pull the data into column B. But the thing is I would like to skip over the cell if it states "All Other" yet continue on the same sequence. I remember writing a formula a while ago for this, had some nested If statements, but cant seem to recall it now.. Please help!


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Original[/TD]
[TD][/TD]
[TD]What I Need[/TD]
[/TR]
[TR]
[TD]Apple
[/TD]
[TD]=IF(A7="All Other",A8,A7)....?[/TD]
[TD]Apple[/TD]
[/TR]
[TR]
[TD]HP[/TD]
[TD][/TD]
[TD]HP[/TD]
[/TR]
[TR]
[TD]Samsung[/TD]
[TD][/TD]
[TD]Samsung
[/TD]
[/TR]
[TR]
[TD]All Other[/TD]
[TD][/TD]
[TD]Sony[/TD]
[/TR]
[TR]
[TD]Sony[/TD]
[TD][/TD]
[TD]Dell[/TD]
[/TR]
[TR]
[TD]Dell[/TD]
[TD][/TD]
[TD]LG[/TD]
[/TR]
[TR]
[TD]LG[/TD]
[TD][/TD]
[TD]Vizio[/TD]
[/TR]
[TR]
[TD]Vizio[/TD]
[TD][/TD]
[TD]Canon[/TD]
[/TR]
[TR]
[TD]Canon[/TD]
[TD][/TD]
[TD]Lenovo[/TD]
[/TR]
[TR]
[TD]Lenovo[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Scott T]

Re: Need a formula to skip a certain value and continue on the sequence

If your original is in A2:A11
This is an array formula and must be committed with CONTROL+SHIFT+ENTER. If done correctly Excel will put {} around the formula.

=IF(ROWS($C$2:C2)>=COUNTIF(A2:A11,"<>all other"),"",INDEX($A$2:$A$11,SMALL(IF($A$2:$A$11<>"all other",ROW($A$2:$A$11)-ROW($A$2)+1),ROWS($C$2:C2))))

 

[Fantoi]

Re: Need a formula to skip a certain value and continue on the sequence

Thanks for replying!

Original is in A2:A11, When C&P-ing your formula into C2 and dragging it down to C10, I am returning a #NUM !

 

[Fantoi]

Re: Need a formula to skip a certain value and continue on the sequence

I'm sorry didn't read through the bolded part... that it was an array formula! IT WORKS THANKS!

 

[Fantoi]

Formula to skip two certain values and continue on the sequence

Hi Everyone,

I have a sequence in column A and would like to pull the data into column B. But the thing is I would like to skip over the cell if it states "All Other Brands" or "Private Label"yet continue on the same sequence. I remember writing a formula a while ago for this, had some nested If statements, but cant seem to recall it now.. Please help!

Please see example down below for reference, thanks!

[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[/TR]
[TR]
[TD]Rank[/TD]
[TD]Original[/TD]
[TD][/TD]
[TD]What I need[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]Apple[/TD]
[TD][/TD]
[TD]Apple[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]HP[/TD]
[TD][/TD]
[TD]HP[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]Samsung[/TD]
[TD][/TD]
[TD]Samsung[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]All Other Brands[/TD]
[TD]SKIP[/TD]
[TD]Sony[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]Sony[/TD]
[TD][/TD]
[TD]Dell[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD]Private Label[/TD]
[TD]SKIP[/TD]
[TD]LG[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD]Dell[/TD]
[TD][/TD]
[TD]Vizio[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD]LG[/TD]
[TD][/TD]
[TD]Canon[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD]Vizio[/TD]
[TD][/TD]
[TD]Lenovo[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD]Canon[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD]Lenovo[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[wideboydixon]

Re: Formula to skip two certain values and continue on the sequence

Book1
	A	B	C	D	E
1	Rank	Original		What I need
2	1	Apple		Apple	1
3	2	HP		HP	2
4	3	Samsung		Samsung	3
5	4	All Other Brands		Sony	4
6	5	Sony		Dell	5
7	6	Private Label		LG	6
8	7	Dell		Vizio	7
9	8	LG		Canon	8
10	9	Vizio		Lenovo	9
11	10	Canon
12	11	Lenovo
Sheet1

Cell Formulas
Range	Formula
D2	{=IFERROR(INDEX($B$2:$B$12,SMALL(IF($B$2:$B$12<>"All Other Brands",IF($B$2:$B$12<>"Private Label",ROW($B$2:$B$12)-ROW($B$1))),ROWS($D$2:$D2))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

WBD

 

[Fantoi]

Re: Formula to skip two certain values and continue on the sequence

thanks WBD!

 

[Scott T]

Re: Need a formula to skip a certain value and continue on the sequence

So that others that may have the same question can see this I am responding to your PM here. To skip Both "All others" and " private label" you can modify the original formula to the below.


Excel 2010[TABLE="class: grid, width: 500"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[/TR]
[TR]
[TD="align: center"]1
[/TD]
[TD="align: right"][/TD]
[TD]Original
[/TD]
[TD="align: right"][/TD]
[TD]List
[/TD]
[/TR]
[TR]
[TD="align: center"]2
[/TD]
[TD="align: right"]1
[/TD]
[TD]Apple
[/TD]
[TD="align: right"][/TD]
[TD]Apple
[/TD]
[/TR]
[TR]
[TD="align: center"]3
[/TD]
[TD="align: right"]2
[/TD]
[TD]HP
[/TD]
[TD="align: right"][/TD]
[TD]HP
[/TD]
[/TR]
[TR]
[TD="align: center"]4
[/TD]
[TD="align: right"]3
[/TD]
[TD]Samsung
[/TD]
[TD="align: right"][/TD]
[TD]Samsung
[/TD]
[/TR]
[TR]
[TD="align: center"]5
[/TD]
[TD="align: right"]4
[/TD]
[TD]All Other
[/TD]
[TD="align: right"][/TD]
[TD]Sony
[/TD]
[/TR]
[TR]
[TD="align: center"]6
[/TD]
[TD="align: right"]5
[/TD]
[TD]Sony
[/TD]
[TD="align: right"][/TD]
[TD]Dell
[/TD]
[/TR]
[TR]
[TD="align: center"]7
[/TD]
[TD="align: right"]6
[/TD]
[TD]Private label
[/TD]
[TD="align: right"][/TD]
[TD]LG
[/TD]
[/TR]
[TR]
[TD="align: center"]8
[/TD]
[TD="align: right"]7
[/TD]
[TD]Dell
[/TD]
[TD="align: right"][/TD]
[TD]Vizio
[/TD]
[/TR]
[TR]
[TD="align: center"]9
[/TD]
[TD="align: right"]8
[/TD]
[TD]LG
[/TD]
[TD="align: right"][/TD]
[TD]Canon
[/TD]
[/TR]
[TR]
[TD="align: center"]10
[/TD]
[TD="align: right"]9
[/TD]
[TD]Vizio
[/TD]
[TD="align: right"][/TD]
[TD]Lenovo
[/TD]
[/TR]
[TR]
[TD="align: center"]11
[/TD]
[TD="align: right"]10
[/TD]
[TD]Canon
[/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]12
[/TD]
[TD="align: right"]11
[/TD]
[TD]Lenovo
[/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Sheet5
​

Copy the formula down
[TABLE="width: 85%"]
<tbody>[TR]
[TD]
Array Formulas[TABLE="class: grid, width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TD="width: 10"]Cell
[/TD]
[TD="align: left"]Formula
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D2
[/TH]
[TD="align: left"]{=IF(ROWS($D$2:D2)>COUNTIFS($B$2:$B$12,"<>all other",$B$2:$B$12,"<>Private label"),"",INDEX($B$2:$B$12,SMALL(IF($B$2:$B$12<>"all other",IF($B$2:$B$12<>"private label",ROW($B$2:$B$12)-ROW($B$2)+1)),ROWS($D$2:D2))))}
[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[Joe4]

Re: Need a formula to skip a certain value and continue on the sequence

I have merged your two threads together. Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread. Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).

Also please note rule 4, as it pertains to requesting assistance via Private Messaging.