To fill 2 target ranges matching certain conditions

H

hsandeep

Well-known Member
Joined
Dec 6, 2008
Messages
1,217
Office Version
  1. 2010
Platform
  1. Windows
  2. Mobile
My first source range is E25:J25, contiguous columns, generates either 1 or 0.
Second source range is E74:J88 generates numerical integers >=0

I need formulas for the third target range E72:J72 & fourth target range and E174:J188.

Third target range E72:J72: to fill the corresponding column cell of E72:J72 with 20 corresponding to the column in E25:J25 in which last occurrence of 1 is found. Example: E25:J25 {0, 1, 0, 0 1, 0} so last occurrence is in I25, hence I72 should be filled with 20.

All preceding columns in E72:J72 to this ‘identified’ column should be filled with 10. As per example considered: E72:H72 should be filled with 10.

And rest succeeding cells, if at all, should be filled with 0. So J72 should be filled with 0. So as per example, E72:J72 should be {10, 10, 10, 10, 20, 0}.

Note: If the last occurrence is in E25 then there would be zero numbers of preceding columns in E72:J72, so in this case E72:J72 should be {20, 0, 0, 0, 0, 0}. If E25:J25=0 then E72:J72 should be {0, 0, 0, 0, 0, 0}.

Filling of corresponding columns in E174:J188:
Fourth target range E174:J188: Corresponding to the column containing 20 in E72:J72, fill corresponding column of E174:J188 with same values of E74:J88.

Filling of preceding columns in E174:J188:

Match each cell’s value of each row of each preceding columns of E74:J88 (cell containing 10 in E72:J72) with the first row cell’s value of the ‘identified’ column in E74:J88. If the values match, fill this value in the corresponding cell of the corresponding preceding column in E174:J188 else match cell’s value of second row of this preceding column of E74:J88 (cell containing 10 in E72:J72) with the first row cell’s value of the ‘identified’ column in E74:J88 again. If they match, fill this value in the corresponding cell of the corresponding preceding column in E174:J188 & so on till 15th row unless values are matched. If no match is found, then fill the corresponding cell of the corresponding preceding column in E174:J188 with 0.

Repeat matching with the second row cell’s value of the ‘identified’ column in E74:J88 till 15th row cell’s value of the ‘identified’ column in E74:J88.

Repeat for each preceding columns.

Note: Cell value in each row is distinct within every column of E74:J88

How to accomplish?
Thanks in advance.
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Sort by date Sort by votes
Hi there,
first of all: what did you try so far? I see you have a good idea of what you want, but miss the formulas you tried... Secondly: it's a pity you are using Office 2010, as 365 has some nice new formulas that make this much easier...
Having said that:
This page Find the Last Occurrence of a Lookup Value a List in Excel gives an idea how to set up a formula to find the last value in a series (your "last occurrence of 1"). Using that in your case:
Excel Formula: 
=SUMPRODUCT(MAX(COLUMN($E$25:$J$25)*($E$25:$J$25=1)))
That returns the column number of the last occurrence of 1. So say you put that formula in A1, you can follow up with:
Excel Formula: 
=IF(COLUMN()=$A$1,20,IF(COLUMN()<$A$1,10,0))
That should give you the formulas for E72:J72 and hopefully give you an idea how to build the formulas for your second range yourself, but feel free to post what you tried when you get stuck.
Koen
 
Upvote 1
Rijnsent said:
That should give you the formulas for E72:J72 and hopefully give you an idea how to build the formulas for your second range yourself, but feel free to post what you tried when you get stuck.
Koen
Click to expand...
Hi Koen, I can understand you have capability not only in Excel but also to analyze a person's 'real' requirement. As far as formula for E72:J72, I am able to achieve successfully by using (for this I had changed E25:J25 to E69:J69):::the formula is
Excel Formula: 
=IF(COLUMN(E$69)<=LOOKUP(2,1/(E$69:J$69=1),COLUMN(E$69:J$69)),IF(COLUMN(E$69)=LOOKUP(2,1/(E$69:J$69=1),COLUMN(E$69:J$69)),20,10),0)
in E72 & then copied across till J72.
I need solution for (a) Filling of corresponding columns in E174:J188 & (b) Filling of all preceding columns, if any, in E174:J188 & (c) Filling of all succeeding columns, if any, in E174:J188
 
Upvote 0
I have now E72:J72 finally generating either of 20 or 10 or 0. Example: E72:J72 could be {10, 10, 10, 10, 20, 0}. (I don't need any formulas for E72:J72)

My pending ‘real’ requirement is filling of the target range E174:J188 with values from E74:J88 based on 3 criteria’s as explained above.

For doing this, I have an idea

Create 3 helper ranges:
EE74:EJ88 to be used for filling values from E74:J88 from its PRECEEDING COLUMNS
FE74:FJ88 to be used for filling values from E74:J88 from its IDENTIFIED COLUMNS
KE74:KJ88 to be used for filling values from E74:J88 from its SUCCEEDING COLUMNS

And then get the corresponding values finally into the target range E174:J188 from these 3 helper ranges created.
Note:
PRECEEDING COLUMNS are corresponding columns in the E74:J88 corresponding to 10 in the E72:J72
IDENTIFIED COLUMNS are corresponding columns in the E74:J88 corresponding to 20 in the E72:J72
SUCCEEDING COLUMNS are corresponding columns in the E74:J88 corresponding to 0 in the E72:J72

Also Note:
There could be a situation in which the number of PRECEEDING COLUMNS are 0 if 20 is in E72
There could be a situation in which the number of SUCCEEDING COLUMNS are 0 if 20 is in J72
Maximum number of IDENTIFIED COLUMNS is 1

How to accomplish please?
Queries are most welcomed. Please help.
 
Upvote 0
You must log in or register to reply here.

Similar threads

W
  • Solved
Code for rows of cells returning reverse-order values.
Replies
5
Views
231
Worksong
W
H
  • Question
VBA to Create a speaker object
Replies
0
Views
168
hsandeep
H
S
  • Question
Conditional formatting
Replies
7
Views
414
Peter_SSs
Peter_SSs
H
  • Question
Get values from a range based on certain conditions
Replies
5
Views
159
Peter_SSs
Peter_SSs
G
  • Solved
Format & Protect range of cells using VBA
Replies
7
Views
430
Gajendran Yadhav
G

Forum statistics

Threads
1,216,500
Messages
6,131,015
Members
449,615
Latest member
Nic0la

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