Extracting all values greater and less than 1

J

John1989

New Member
Joined
Aug 21, 2017
Messages
22
Hi friends,

I have the following data from cell A1 to E11. I want to extract all the Product name which is >=1 and <=-1 for chennai in B15, and for Mumbai in C15, for Delhi D15 and for Bangalore E5.

I wish to do this using excel formulas. Please help :confused::)

[TABLE="width: 200"]
<tbody>[TR]
[TD]Porudct[/TD]
[TD]Sales rate Chennai[/TD]
[TD]Sales rate Mumbai[/TD]
[TD]Sales rate Delhi[/TD]
[TD]Sales rate Bangalore[/TD]
[/TR]
[TR]
[TD][TABLE="width: 118"]
<tbody>[TR]
[TD="class: xl65, width: 118"]Tv[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 118"]
<tbody>[TR]
[TD="class: xl65, width: 118"]13.6[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 118"]
<tbody>[TR]
[TD="class: xl65, width: 118"]11.5[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 118"]
<tbody>[TR]
[TD="class: xl65, width: 118"]8[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 118"]
<tbody>[TR]
[TD="class: xl65, width: 118"]-0.1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 118"]
<tbody>[TR]
[TD="class: xl65, width: 118"]Fan[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 118"]
<tbody>[TR]
[TD="class: xl65, width: 118"]0.2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 118"]
<tbody>[TR]
[TD="class: xl65, width: 118"]0[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 118"]
<tbody>[TR]
[TD="class: xl65, width: 118"]-0.5[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 118"]
<tbody>[TR]
[TD="class: xl65, width: 118"]-2.5[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 118"]
<tbody>[TR]
[TD="class: xl65, width: 118"]Washing machine[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 118"]
<tbody>[TR]
[TD="class: xl65, width: 118"]-3.4[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 118"]
<tbody>[TR]
[TD="class: xl65, width: 118"]2.5[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 118"]
<tbody>[TR]
[TD="class: xl65, width: 118"]0[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 118"]
<tbody>[TR]
[TD="class: xl65, width: 118"]4.7[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 118"]
<tbody>[TR]
[TD="class: xl65, width: 118"]Mobile[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 118"]
<tbody>[TR]
[TD="class: xl65, width: 118"]22.6[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 118"]
<tbody>[TR]
[TD="class: xl65, width: 118"]10.4[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 118"]
<tbody>[TR]
[TD="class: xl65, width: 118"]8.7[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 118"]
<tbody>[TR]
[TD="class: xl65, width: 118"]3.6[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 118"]
<tbody>[TR]
[TD="class: xl65, width: 118"]Laptop[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 118"]
<tbody>[TR]
[TD="class: xl65, width: 118"]0[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 118"]
<tbody>[TR]
[TD="class: xl65, width: 118"]-0.2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 118"]
<tbody>[TR]
[TD="class: xl65, width: 118"]5[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 118"]
<tbody>[TR]
[TD="class: xl65, width: 118"]-3.5[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 118"]
<tbody>[TR]
[TD="class: xl65, width: 118"]Tablet[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 118"]
<tbody>[TR]
[TD="class: xl65, width: 118"]-8.9[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 118"]
<tbody>[TR]
[TD="class: xl65, width: 118"]8.5[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 118"]
<tbody>[TR]
[TD="class: xl65, width: 118"]10.3[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 118"]
<tbody>[TR]
[TD="class: xl65, width: 118"]0[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 118"]
<tbody>[TR]
[TD="class: xl65, width: 118"]Fridge[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 118"]
<tbody>[TR]
[TD="class: xl65, width: 118"]0.7[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 118"]
<tbody>[TR]
[TD="class: xl65, width: 118"]3.5[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 118"]
<tbody>[TR]
[TD="class: xl65, width: 118"]-4[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 118"]
<tbody>[TR]
[TD="class: xl65, width: 118"]-2.4[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 118"]
<tbody>[TR]
[TD="class: xl65, width: 118"]Mixer grainder[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 118"]
<tbody>[TR]
[TD="class: xl65, width: 118"]-3.5[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 118"]
<tbody>[TR]
[TD="class: xl65, width: 118"]0.9[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]2[/TD]
[TD][TABLE="width: 118"]
<tbody>[TR]
[TD="class: xl65, width: 118"]0.4[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 118"]
<tbody>[TR]
[TD="class: xl65, width: 118"]Clock[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 118"]
<tbody>[TR]
[TD="class: xl65, width: 118"]-0.6[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 118"]
<tbody>[TR]
[TD="class: xl65, width: 118"]7.9[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 118"]
<tbody>[TR]
[TD="class: xl65, width: 118"]4.6[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 118"]
<tbody>[TR]
[TD="class: xl65, width: 118"]-3.4[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 118"]
<tbody>[TR]
[TD="class: xl65, width: 118"]Speaker[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 118"]
<tbody>[TR]
[TD="class: xl65, width: 118"]-4.4[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 118"]
<tbody>[TR]
[TD="class: xl65, width: 118"]0.3[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 118"]
<tbody>[TR]
[TD="class: xl65, width: 118"]-0.2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 118"]
<tbody>[TR]
[TD="class: xl65, width: 118"]2.1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Sort by date Sort by votes
Give this a try... put this formula in cell B15 and copy it across to cell E15.

=MID(IF(ABS(B2)>=1,", "&$A2,"")&IF(ABS(B3)>=1,", "&$A3,"")&IF(ABS(B4)>=1,", "&$A4,"")&IF(ABS(B5)>=1,", "&$A5,"")&IF(ABS(B6)>=1,", "&$A6,"")&IF(ABS(B7)>=1,", "&$A7,"")&IF(ABS(B8)>=1,", "&$A8,"")&IF(ABS(B9)>=1,", "&$A9,"")&IF(ABS(B10)>=1,", "&$A10,"")&IF(ABS(B11)>=1,", "&$A11,""),3,300)
 
Upvote 0
Also, if your Excel lists the TEXTJOIN function...

In B15 control+shift+enter, not just enter, and copy across:

=TEXTJOIN(",",TRUE,IF((B$2:B$11>=1)+(B$2:B$11<=-1),$A$2:$A$11,""))
 
Upvote 0
Rick Rothstein said:
Give this a try... put this formula in cell B15 and copy it across to cell E15.

=MID(IF(ABS(B2)>=1,", "&$A2,"")&IF(ABS(B3)>=1,", "&$A3,"")&IF(ABS(B4)>=1,", "&$A4,"")&IF(ABS(B5)>=1,", "&$A5,"")&IF(ABS(B6)>=1,", "&$A6,"")&IF(ABS(B7)>=1,", "&$A7,"")&IF(ABS(B8)>=1,", "&$A8,"")&IF(ABS(B9)>=1,", "&$A9,"")&IF(ABS(B10)>=1,", "&$A10,"")&IF(ABS(B11)>=1,", "&$A11,""),3,300)
Click to expand...



Thank you friend, This is gives all data in one single cell B15 for Chennai, C15 for Mumbai and so on. But I would like to get the data one below one like B15, B16, B17, etc for Chennai, C15, C16, C17 for mumbai etc.

For example

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]TV[/TD]
[TD]TV[/TD]
[TD]TV[/TD]
[TD]Fan[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]Washing machine[/TD]
[TD]Washine machine[/TD]
[TD]Mobile [/TD]
[TD]Washing machin[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]Mobile[/TD]
[TD]mobile[/TD]
[TD]Laptop[/TD]
[TD]Mobile[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]Tablet[/TD]
[TD]Tablet[/TD]
[TD]Tablet[/TD]
[TD]Laptop[/TD]
[/TR]
</tbody>[/TABLE]

Please help.............

Thanks in advance
 
Upvote 0
Thank you friend, but this gives #NAME? error

I would like to get the data one below one like B15, B16, B17, etc for Chennai, C15, C16, C17 for mumbai etc.
For example

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]TV[/TD]
[TD]TV[/TD]
[TD]TV[/TD]
[TD]Fan[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]Washing machine[/TD]
[TD]Washine machine[/TD]
[TD]Mobile [/TD]
[TD]Washing machin[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]Mobile[/TD]
[TD]mobile[/TD]
[TD]Laptop[/TD]
[TD]Mobile[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]Tablet[/TD]
[TD]Tablet[/TD]
[TD]Tablet[/TD]
[TD]Laptop[/TD]
[/TR]
</tbody>[/TABLE]

Please help.............

Thanks in advance
 
Upvote 0
Aladin Akyurek said:
Also, if your Excel lists the TEXTJOIN function...

In B15 control+shift+enter, not just enter, and copy across:

=TEXTJOIN(",",TRUE,IF((B$2:B$11>=1)+(B$2:B$11<=-1),$A$2:$A$11,""))
Click to expand...

Thank you friend, but this gives #NAME? error

I would like to get the data one below one like B15, B16, B17, etc for Chennai, C15, C16, C17 for mumbai etc.
For example

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]TV[/TD]
[TD]TV[/TD]
[TD]TV[/TD]
[TD]Fan[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]Washing machine[/TD]
[TD]Washine machine[/TD]
[TD]Mobile [/TD]
[TD]Washing machin[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]Mobile[/TD]
[TD]mobile[/TD]
[TD]Laptop[/TD]
[TD]Mobile[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]Tablet[/TD]
[TD]Tablet[/TD]
[TD]Tablet[/TD]
[TD]Laptop[/TD]
[/TR]
</tbody>[/TABLE]

Please help.............

Thanks in advance
 
Upvote 0
In B15 control+shift+enter, not just enter, copy across, and down:

=IFERROR(INDEX($A$2:$A$11,SMALL(IF((B$2:B$11>=1)+(B$2:B$11<=-1),ROW($A$2:$A$11)-ROW($A$2)+1),ROWS(B$15:B15))),"")
 
Upvote 0
Aladin Akyurek said:
In B15 control+shift+enter, not just enter, copy across, and down:

=IFERROR(INDEX($A$2:$A$11,SMALL(IF((B$2:B$11>=1)+(B$2:B$11<=-1),ROW($A$2:$A$11)-ROW($A$2)+1),ROWS(B$15:B15))),"")
Click to expand...

Same formula is coming in all cells like B15 B16 C15 C16 etc. Formula is not changing according to cells. Results give "TV " in all the cells B15 B16 C15 C16

Please help
 
Upvote 0
John1989 said:
Same formula is coming in all cells like B15 B16 C15 C16 etc. Formula is not changing according to cells. Results give "TV " in all the cells B15 B16 C15 C16

Please help
Click to expand...
Did you do this after putting the formula into the cell... "In B15 control+shift+enter, not just enter.."
 
Upvote 0
Rick Rothstein said:
Did you do this after putting the formula into the cell... "In B15 control+shift+enter, not just enter.."
Click to expand...


First i have selected B15 then F2, pasted the formula then ctrl + shift + enter. This converted as array {}
Then I have selected all the required cells from B15 to D17 then F2 then i tried ctrl + shift + enter.

But same formula is coming in all the cells with { }
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Matrix returns wrong values
Replies
3
Views
400
Peter_SSs
Peter_SSs
L
  • Solved
INDEX MATCH MAXIFS / AGGREGATE - Multiple criteria
Replies
3
Views
494
Fluff
Fluff
C
  • Solved
Create Function that Calculates a Value Relative to Original Price and Also Constructs a Price Ladder
2
Replies
10
Views
1K
NdNoviceHlp
NdNoviceHlp
A
  • Question Question
Sales Related Questions
Replies
4
Views
673
anneb_87
A
A
  • Question Question
Use VBA to find slope =0
Replies
4
Views
2K
artz
A

Forum statistics

Threads
1,223,911
Messages
6,175,324
Members
452,635
Latest member
laura12345

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top