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!

Original		What I Need
Apple	=IF(A7="All Other",A8,A7)....?	Apple
HP		HP
Samsung		Samsung
All Other		Sony
Sony		Dell
Dell		LG
LG		Vizio
Vizio		Canon
Canon		Lenovo
Lenovo

<tbody>
</tbody>

 

[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!

Rank	Original		What I need
1	Apple		Apple	1
2	HP		HP	2
3	Samsung		Samsung	3
4	All Other Brands	SKIP	Sony	4
5	Sony		Dell	5
6	Private Label	SKIP	LG	6
7	Dell		Vizio	7
8	LG		Canon	8
9	Vizio		Lenovo	9
10	Canon
11	Lenovo

<tbody>
</tbody>

<tbody>
</tbody>

 

[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

	A	B	C	D
1		Original		List
2	1	Apple		Apple
3	2	HP		HP
4	3	Samsung		Samsung
5	4	All Other		Sony
6	5	Sony		Dell
7	6	Private label		LG
8	7	Dell		Vizio
9	8	LG		Canon
10	9	Vizio		Lenovo
11	10	Canon
12	11	Lenovo

<tbody>
</tbody>

Sheet5
​

Copy the formula down

Array Formulas Cell Formula D2 {=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))))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[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.