Need formula or advice how to solve this in excel

[Siga]

Hello excel ninjas,

Need your help. Please find my excel spreed sheet attached.
I have an excel table with the result after comparing to other excel tables. Now I need to extract only cells which have value >0 and my Product1/Product4/Product5/etc row from horizontal view should become a vertical row.
I put the result I want to get just below excel table.
Any ideas - Formulas? or what further steps I need to make in order to get this?
Thank you very much in advance!
Have a relaxed and lovely Saturday.

Siga

 

[Siga]

In my example trade=3rd party. Forgot to change the description in my result tab while preparing example.

 

[vletm]

Siga - just Press [Over Zero]-button
Did You really mean as You wrote over ZERO?
If so, then Your 'results' has some ... or what?
Check my sample and ... ask if needed?
Ps. That 'my result' comes always below few rows under the lowest row!

 

[bosco_yip]

Hi,

Please see attached file.

Regards
Bosco

 

[Siga]

vletm, looks fantastic! Actually I need all the values except blanks and 0. And saying 0 I mean the ones that are <> or equal 1. In other word I do not need values like 0.005 or 0.1.

Also, how you build that Macros? you already built in the solution, but how to "copu paste" it into my working sheets?

 

[Siga]

bosco-vip, I really feel like jumping into ninja world. THANK YOU! need to study your masterpiece and comeback. but it blew my mind!

 

[vletm]

Siga
1) value >0 means over 0 ...
maybe You mean abs(value) >= 1 ... or how? It's possible adjust as needs.
2) You can use it ...
2a) Move mouse over that button
2b) press 'right-mouse'
2c) select 'Assign Macro...'
2d) press [Edit]
2e) there is Macro for You
2f) copy that to Your 'needed' file as in sample
2g) data's layout have to be just as in sample
2h) no need to worry, if You add few hundreds more rows or so -
just press the button!
> Ideas ... Questions?

 

[Siga]

Siga
1) value >0 means over 0 ...
maybe You mean abs(value) >= 1 ... or how? It's possible adjust as needs.

Looks like I am not good at speaking Excel language, but looks like you got me right. I need values over 1 or below -1 (if the value is negative). And I do not need values between 1 & -1.

> Ideas ... Questions?

I will work today to try to built into my excel. No Questions for now, only BIG THANK YOU!!

 

[vletm]

Siga - good English
... and if You would like to get that 'Result'
... better place then test this too
... and it would work no matter how many <-1 or >1 would come
... even if ALL values would come then just press the button!

 

[Siga]

Hi,

Please see attached file.

Regards
Bosco

Hi Bosco,

I went through your formula and tried in my file. I extended number of countries as well as added 2 products.
My formula looks now like: {=IFERROR(INDIRECT(TEXT(MID(SMALL(IF(((E$4:L$234>0.1)+(E$4:L$234<-0.1));ROW($4:$234)/1%+{5,6,7,8,9,10,11,12}*100001);ROW(A15));2;3)&"02";"R0C00");"")}
I copy paste the formula down to catch all the rows and values, but one row is empty for Columns B/C/D (Sales Channel / Region / Geography accordingly), though catches value and Product name itself.
Any ideas why this happens and how to solve it?
Also, what number in red means in your formula? If I am adding rows or columns, should I take care of this number too, or it always will be 100001?

Siga

 

[Siga]

Bosco, also uploading file for your review and confirmation why this happened.
Thank you!

 

[bosco_yip]

Bosco, also uploading file for your review and confirmation why this happened.
Thank you!

Hi,

The problem have fixed as per attached file

Regards
Bosco

 

[Siga]

Hi,

The problem have fixed as per attached file

Regards
Bosco

Bosco, THANK YOU! Works perfectly. You made my Monday sunny

 

[Siga]

Siga

2g) data's layout have to be just as in sample
2h) no need to worry, if You add few hundreds more rows or so -
just press the button!
> Ideas ... Questions?

Hi vletm,

I like your second version better as it is below the table. The thing is that I usually have to add some products (columns to the right). It might vary from 5 to 15.
Do I understand correctly if I add columns, I need to change only this condition
"For x = 5 To 10" in your Macros. In Other words - what should I know about Macros if I am adding columns to the right?

Siga

 

[vletm]

Siga - Okay
It works a bit different way ...
but if You would this more easier then
'just' take care that there are NO EMPTY columns between 'Product's.
You can add as many 'Products' as needed and
as many rows as needed.
No need to make any changes to anywhere.
'Just' Click the [Over Zero] button and
You will get a result in next sheet.
Ps. and just for example ...
if You one day will have all values to show to 'result'
even then You 'just' click the button!
I filled 'some values' there ... and test
after that You can get Your 'original values' back with right side button.

 

[Siga]

Siga - Okay

Mind blowing! Is there a way we could exchange the brains - you and I? :D

 

[vletm]

Siga ... only in Your dreams