REVERSE the data corresponding to value in the flag cell

hsandeep

Well-known Member
Joined
Dec 6, 2008
Messages
959
Office Version
  1. 2007
Platform
  1. Windows
My Data range is B2:F11 with a flag in corresponding row cell in A2:F11
This flag in A2:F11 is EITHER of 1 OR 0
Output required as in H2:L11 as:
If flag is 1, then corresponding row in H2:L11 SHOULD BE ‘SAME’
If flag is 0, then corresponding row in H2:L11 SHOULD BE ‘REVERSED’

How to accomplish?
Thanks in advance
I am using Excel 2007
Sheet1

ABCDEFGHIJKL
21778899100200 778899100200
302233445566 6655443322
4
5
6
7
8
9
10
11144125052 44125052

<colgroup><col style="FONT-WEIGHT: bold; WIDTH: 30px"><col style="WIDTH: 64px"><col style="WIDTH: 40px"><col style="WIDTH: 40px"><col style="WIDTH: 40px"><col style="WIDTH: 40px"><col style="WIDTH: 40px"><col style="WIDTH: 10px"><col style="WIDTH: 40px"><col style="WIDTH: 40px"><col style="WIDTH: 40px"><col style="WIDTH: 40px"><col style="WIDTH: 40px"></colgroup><tbody>
</tbody>
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
60,197
Office Version
  1. 365
Platform
  1. Windows
In h2 copied down & across
=IF($A2="","",IF($A2=1,B2,INDEX($B2:$F2,COLUMN($F2)-COLUMN(A2))))
 

hsandeep

Well-known Member
Joined
Dec 6, 2008
Messages
959
Office Version
  1. 2007
Platform
  1. Windows
In h2 copied down & across
=IF($A2="","",IF($A2=1,B2,INDEX($B2:$F2,COLUMN($F2)-COLUMN(A2))))

Fluff
Just now...while re-trying........I 'inserted' 3 columns so that Column A got shifted to Column D; BUT I am getting #REF ! in the output cell!!!

Sheet1

DEFGHIJKLMNOPQ
20 778899100200 #REF !100998877
30 2250222222 #REF !22225022
4
5
6
7
8
9
10
110 22222 #REF !2222

<colgroup><col style="FONT-WEIGHT: bold; WIDTH: 30px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 40px"><col style="WIDTH: 40px"><col style="WIDTH: 40px"><col style="WIDTH: 40px"><col style="WIDTH: 40px"><col style="WIDTH: 10px"><col style="WIDTH: 46px"><col style="WIDTH: 40px"><col style="WIDTH: 40px"><col style="WIDTH: 40px"><col style="WIDTH: 40px"></colgroup><tbody>
</tbody>

Spreadsheet Formulas
CellFormula
M2=IF($D2="","",IF($D2=1,G2,INDEX($G2:$K2,COLUMN($K2)-COLUMN(D2))))
N2=IF($D2="","",IF($D2=1,H2,INDEX($G2:$K2,COLUMN($K2)-COLUMN(G2))))
O2=IF($D2="","",IF($D2=1,I2,INDEX($G2:$K2,COLUMN($K2)-COLUMN(H2))))
P2=IF($D2="","",IF($D2=1,J2,INDEX($G2:$K2,COLUMN($K2)-COLUMN(I2))))
Q2=IF($D2="","",IF($D2=1,K2,INDEX($G2:$K2,COLUMN($K2)-COLUMN(J2))))
M3=IF($D3="","",IF($D3=1,G3,INDEX($G3:$K3,COLUMN($K3)-COLUMN(D3))))
N3=IF($D3="","",IF($D3=1,H3,INDEX($G3:$K3,COLUMN($K3)-COLUMN(G3))))
O3=IF($D3="","",IF($D3=1,I3,INDEX($G3:$K3,COLUMN($K3)-COLUMN(H3))))
P3=IF($D3="","",IF($D3=1,J3,INDEX($G3:$K3,COLUMN($K3)-COLUMN(I3))))
Q3=IF($D3="","",IF($D3=1,K3,INDEX($G3:$K3,COLUMN($K3)-COLUMN(J3))))
M11=IF($D11="","",IF($D11=1,G11,INDEX($G11:$K11,COLUMN($K11)-COLUMN(D11))))
N11=IF($D11="","",IF($D11=1,H11,INDEX($G11:$K11,COLUMN($K11)-COLUMN(G11))))
O11=IF($D11="","",IF($D11=1,I11,INDEX($G11:$K11,COLUMN($K11)-COLUMN(H11))))
P11=IF($D11="","",IF($D11=1,J11,INDEX($G11:$K11,COLUMN($K11)-COLUMN(I11))))
Q11=IF($D11="","",IF($D11=1,K11,INDEX($G11:$K11,COLUMN($K11)-COLUMN(J11))))

<tbody>
</tbody>

<tbody>
</tbody>

Please check.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
60,197
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

The problem is that you have inserted two blank columns in E & G, not the blank columns in A:C.
What is you actual layout?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
60,197
Office Version
  1. 365
Platform
  1. Windows
With your original layout change the formula to
=IF($A2="","",IF($A2=1,B2,INDEX($B2:$F2,COLUMN($G2)-COLUMN(B2))))
 

hsandeep

Well-known Member
Joined
Dec 6, 2008
Messages
959
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

With your original layout change the formula to
=IF($A2="","",IF($A2=1,B2,INDEX($B2:$F2,COLUMN($G2)-COLUMN(B2))))
Little miss-communication from my side

1. My data range for both INPUT (B2:F11) & OUTPUT (H2:L11) looks ‘similar’ to the ‘actual’ location in the Worksheet. Here I mean instead of B2:F11 it is ‘actually at’ CB2:CF11 & instead of H2:L11 it is ‘actually at’DM2:DQ11.
2. So there are lot of columns BETWEEN INPUT range & OUTPUT range.
3. Both these ranges MAY shift as required in future & the formula in the OUTPUT cells has to take care.

Comments please.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
60,197
Office Version
  1. 365
Platform
  1. Windows
Little miss-communication from my side
That (IMO) the understatement of the year.
If you give us incorrect info, then you will get something that doesn't work in return.

Whilst you have now said where some of the data is located you haven't said where the initial criteria is is located.
 
Last edited:

hsandeep

Well-known Member
Joined
Dec 6, 2008
Messages
959
Office Version
  1. 2007
Platform
  1. Windows
That (IMO) the understatement of the year.
If you give us incorrect info, then you will get something that doesn't work in return.

Whilst you have now said where some of the data is located you haven't said where the initial criteria is is located.

The initial criteria is located at U2:U11
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,473
Office Version
  1. 365
Platform
  1. Windows
This, copied across and down, should be readily adaptable to work with the 3 sections anywhere.

<b>Excel 365</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th><th>O</th><th>P</th><th>Q</th><th>R</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="text-align: right;;">77</td><td style="text-align: right;;">88</td><td style="text-align: right;;">99</td><td style="text-align: right;;">100</td><td style="text-align: right;;">200</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style="text-align: right;;">22</td><td style="text-align: right;;">33</td><td style="text-align: right;;">44</td><td style="text-align: right;;">55</td><td style="text-align: right;;">66</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">20</td><td style="text-align: right;;">5</td><td style="text-align: right;;">6</td><td style="text-align: right;;">8</td><td style="text-align: right;;">9</td><td style="text-align: right;;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">21</td><td style="text-align: right;;">44</td><td style="text-align: right;;">12</td><td style="text-align: right;;">50</td><td style="text-align: right;;">5</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">22</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">23</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">24</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">77</td><td style="text-align: right;;">88</td><td style="text-align: right;;">99</td><td style="text-align: right;;">100</td><td style="text-align: right;;">200</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">25</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">66</td><td style="text-align: right;;">55</td><td style="text-align: right;;">44</td><td style="text-align: right;;">33</td><td style="text-align: right;;">22</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">32</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">4</td><td style="text-align: right;;">9</td><td style="text-align: right;;">8</td><td style="text-align: right;;">6</td><td style="text-align: right;;">5</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">33</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">44</td><td style="text-align: right;;">12</td><td style="text-align: right;;">50</td><td style="text-align: right;;">5</td><td style="text-align: right;;">2</td></tr></tbody></table><p style="width:9.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Reverse Some</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">N24</th><td style="text-align:left">=IF(<font color="Blue">$K1=1,D12,INDEX(<font color="Red">$D12:$H12,COLUMNS(<font color="Green">$D12:$H12</font>)+1-COLUMNS(<font color="Green">$N24:N24</font>)</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 

Forum statistics

Threads
1,136,909
Messages
5,678,522
Members
419,769
Latest member
Sparks66

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
Top