REVERSE the data corresponding to value in the flag cell

H

hsandeep

Well-known Member
Joined
Dec 6, 2008
Messages
1,226
Office Version
  1. 2010
Platform
  1. Windows
  2. Mobile
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

<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>
[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="bgcolor: #ffcc00, align: right"]1[/TD]
[TD="align: right"]77[/TD]
[TD="align: right"]88[/TD]
[TD="align: right"]99[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]200[/TD]

[TD="bgcolor: #ffff00, align: right"]77[/TD]
[TD="bgcolor: #ffff00, align: right"]88[/TD]
[TD="bgcolor: #ffff00, align: right"]99[/TD]
[TD="bgcolor: #ffff00, align: right"]100[/TD]
[TD="bgcolor: #ffff00, align: right"]200[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="bgcolor: #ffcc00, align: right"]0[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]66[/TD]

[TD="bgcolor: #ffff00, align: right"]66[/TD]
[TD="bgcolor: #ffff00, align: right"]55[/TD]
[TD="bgcolor: #ffff00, align: right"]44[/TD]
[TD="bgcolor: #ffff00, align: right"]33[/TD]
[TD="bgcolor: #ffff00, align: right"]22[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]
[TD="bgcolor: #ffcc00, align: right"]1[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]2[/TD]

[TD="bgcolor: #ffff00, align: right"]44[/TD]
[TD="bgcolor: #ffff00, align: right"]12[/TD]
[TD="bgcolor: #ffff00, align: right"]50[/TD]
[TD="bgcolor: #ffff00, align: right"]5[/TD]
[TD="bgcolor: #ffff00, align: right"]2[/TD]

</tbody>
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Sort by date Sort by votes
In h2 copied down & across
=IF($A2="","",IF($A2=1,B2,INDEX($B2:$F2,COLUMN($F2)-COLUMN(A2))))
 
Upvote 0
Fluff said:
In h2 copied down & across
=IF($A2="","",IF($A2=1,B2,INDEX($B2:$F2,COLUMN($F2)-COLUMN(A2))))
Click to expand...

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

<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>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffcc00]#ffcc00[/URL] , align: right"]0[/TD]

[TD="align: right"]77[/TD]
[TD="align: right"]88[/TD]
[TD="align: right"]99[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]200[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] "] #REF ![/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]100[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]99[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]88[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]77[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffcc00]#ffcc00[/URL] , align: right"]0[/TD]

[TD="align: right"]22[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]22[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] "] #REF ![/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]22[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]22[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]50[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]22[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]9[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]10[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]11[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffcc00]#ffcc00[/URL] , align: right"]0[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] "] #REF ![/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]2[/TD]

</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.
 
Upvote 0
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?
 
Upvote 0
With your original layout change the formula to
=IF($A2="","",IF($A2=1,B2,INDEX($B2:$F2,COLUMN($G2)-COLUMN(B2))))
 
Upvote 0
Fluff said:
With your original layout change the formula to
=IF($A2="","",IF($A2=1,B2,INDEX($B2:$F2,COLUMN($G2)-COLUMN(B2))))
Click to expand...
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.
 
Upvote 0
hsandeep said:
Little miss-communication from my side
Click to expand...
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:
Upvote 0
Fluff said:
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.
Click to expand...

The initial criteria is located at U2:U11
 
Upvote 0
This, copied across and down, should be readily adaptable to work with the 3 sections anywhere.


Excel 365
DEFGHIJKLMNOPQR
11
20
90
101
11
12778899100200
132233445566
2056894
2144125052
22
23
24778899100200
256655443322
3249865
3344125052
Reverse Some
Cell Formulas
RangeFormula
N24=IF($K1=1,D12,INDEX($D12:$H12,COLUMNS($D12:$H12)+1-COLUMNS($N24:N24)))
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question Question
VBA code clean up
Replies
9
Views
482
mumps
mumps
N
  • Solved
Total of marks with the specific conditions
2
Replies
11
Views
389
Eric W
Eric W
R
  • Question Question
Highlighting cells based on criteria
Replies
0
Views
393
rahildhody
R
J
  • Question Question
copying adjacent cells based on value of referenced cell
Replies
4
Views
233
arun_eagertolearn
A
D
  • Solved
ROUNDUP and MIN with IF/AND
Replies
6
Views
191
dreid1011
dreid1011

Forum statistics

Threads
1,223,214
Messages
6,170,774
Members
452,353
Latest member
strainu

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