To fill 2 target ranges matching certain conditions

[hsandeep]

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.

 

[Rijnsent]

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:

=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:

=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

 

[hsandeep]

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

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

=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

 

[hsandeep]

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.