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]
 
Sort by date Sort by votes
John1989 said:
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 { }
Click to expand...

Clear the formula cells and start over.

1. Type (or copy-and-paste) the formula in B15.

=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))),"")

2. Select B15.

3. Go to the formula bar with the mouse pointer (where you see the formula).

4. Press down the control and the shift keys at the same time while you hit the enter key. If done well, Excel itself puts a pair of { and } around the formula in recognition.

5. Now drag the formula to the right and down.
 
Upvote 0

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Aladin Akyurek said:
Clear the formula cells and start over.

1. Type (or copy-and-paste) the formula in B15.

=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))),"")

2. Select B15.

3. Go to the formula bar with the mouse pointer (where you see the formula).

4. Press down the control and the shift keys at the same time while you hit the enter key. If done well, Excel itself puts a pair of { and } around the formula in recognition.

5. Now drag the formula to the right and down.
Click to expand...

Thank you so much..... This gives the data as i want.. thank you again...
 
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,337
Members
452,637
Latest member
Ezio2866

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