Totally lost - Greenhorn question --> filling cells

roth_georg

New Member
Joined
Feb 20, 2008
Messages
37
Totally lost - Greenhorn question </SPAN></SPAN>à</SPAN></SPAN> filling cells</SPAN></SPAN></SPAN>

Hi all</SPAN></SPAN>

I guess I have a real greenhorn question but I did not manage to get this done with regular formulas and I’m not used to macros. I hope you can help.</SPAN></SPAN>

The problem is pretty good described in the picture below.</SPAN></SPAN>


https://plus.google.com/u/0/photos?tab=wq#photos/107223412229072028623/albums/posts


I want to move the values in B3 and C3 to every line where I have value = 1in column A. I can do that quite easily for one block with if (A4=1,B$3). But this does not work for rows after row 11. Here I would need now if (A4=1,B$10). I would need something like if cell value in column A = 2 or T do not consider values above. And so on and so…</SPAN></SPAN>
I guess this needs a bit of macro. Any idea??</SPAN></SPAN>

Thanks a lot for your help.</SPAN></SPAN>
Georg</SPAN></SPAN>
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

TheCman81

Well-known Member
Joined
Feb 28, 2012
Messages
535
Enter this in G4 & H4 (It's an array formual so please use Crt Shift Enter and not just enter, drag down as far as needed) You'll obv have to change to range to suit yours

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">H</td><td style=";">G101</td><td style="text-align: right;;">20130326</td><td style="text-align: right;;">163553</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: #161120;text-align: center;">3</td><td style="text-align: right;;">0</td><td style="text-align: right;background-color: #8DB4E2;;">25</td><td style="text-align: right;background-color: #8DB4E2;;">30</td><td style=";">MY11</td><td style="text-align: right;;">20121231</td><td style=";">MYR</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style=";">DR</td><td style="text-align: right;;"></td><td style="text-align: right;;">0.23</td><td style="text-align: right;;"></td><td style="text-align: right;;">25</td><td style="text-align: right;;">30</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style=";">DR</td><td style="text-align: right;;"></td><td style="text-align: right;;">90</td><td style="text-align: right;;"></td><td style="text-align: right;;">25</td><td style="text-align: right;;">30</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">1</td><td style="text-align: right;;">3</td><td style=";">CR</td><td style="text-align: right;;"></td><td style="text-align: right;;">25.88</td><td style="text-align: right;;"></td><td style="text-align: right;;">25</td><td style="text-align: right;;">30</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">1</td><td style="text-align: right;;">4</td><td style=";">CR</td><td style="text-align: right;;"></td><td style="text-align: right;;">0.23</td><td style="text-align: right;;"></td><td style="text-align: right;;">25</td><td style="text-align: right;;">30</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">1</td><td style="text-align: right;;">5</td><td style=";">CR</td><td style="text-align: right;;"></td><td style="text-align: right;;">64.12</td><td style="text-align: right;;"></td><td style="text-align: right;;">25</td><td style="text-align: right;;">30</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">2</td><td style="text-align: right;;">35</td><td style="text-align: right;;">104192.73</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">0</td><td style="text-align: right;background-color: #8DB4E2;;">26</td><td style="text-align: right;background-color: #8DB4E2;;">31</td><td style=";">MY11</td><td style="text-align: right;;">20121231</td><td style=";">MYR</td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">1</td><td style="text-align: right;;">36</td><td style=";">CR</td><td style="text-align: right;;"></td><td style="text-align: right;;">6590.54</td><td style="text-align: right;;"></td><td style="text-align: right;;">26</td><td style="text-align: right;;">31</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;">1</td><td style="text-align: right;;">37</td><td style=";">DR</td><td style="text-align: right;;"></td><td style="text-align: right;;">2339.64</td><td style="text-align: right;;"></td><td style="text-align: right;;">26</td><td style="text-align: right;;">31</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;">1</td><td style="text-align: right;;">38</td><td style=";">DR</td><td style="text-align: right;;"></td><td style="text-align: right;;">170.56</td><td style="text-align: right;;"></td><td style="text-align: right;;">26</td><td style="text-align: right;;">31</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;">1</td><td style="text-align: right;;">192</td><td style=";">CR</td><td style="text-align: right;;"></td><td style="text-align: right;;">497934.48</td><td style="text-align: right;;"></td><td style="text-align: right;;">26</td><td style="text-align: right;;">31</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;">1</td><td style="text-align: right;;">193</td><td style=";">DR</td><td style="text-align: right;;"></td><td style="text-align: right;;">18948.64</td><td style="text-align: right;;"></td><td style="text-align: right;;">26</td><td style="text-align: right;;">31</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;;">1</td><td style="text-align: right;;">194</td><td style=";">CR</td><td style="text-align: right;;"></td><td style="text-align: right;;">1421.14</td><td style="text-align: right;;"></td><td style="text-align: right;;">26</td><td style="text-align: right;;">31</td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;;">1</td><td style="text-align: right;;">195</td><td style=";">CR</td><td style="text-align: right;;"></td><td style="text-align: right;;">236.86</td><td style="text-align: right;;"></td><td style="text-align: right;;">26</td><td style="text-align: right;;">31</td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: right;;">1</td><td style="text-align: right;;">196</td><td style=";">CR</td><td style="text-align: right;;"></td><td style="text-align: right;;">17290.64</td><td style="text-align: right;;"></td><td style="text-align: right;;">26</td><td style="text-align: right;;">31</td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: right;;">2</td><td style="text-align: right;;">161</td><td style="text-align: right;;">917287.24</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style=";">T</td><td style="text-align: right;;">201</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=";"></td><td style=";"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><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: #DAE7F5;color: #161120">G4</th><td style="text-align:left">{=IF(<font color="Blue">$A4=1,IFERROR(<font color="Red">INDEX(<font color="Green">B$3:B$20,SMALL(<font color="Purple">IF(<font color="Teal">$A$3:$A$20=0,ROW(<font color="#FF00FF">$A$3:$A$20</font>)-ROW(<font color="#FF00FF">$A$3</font>)+1</font>),COUNTIF(<font color="Teal">$A$3:A3,$A$3</font>)</font>)</font>),""</font>),""</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
 

TheCman81

Well-known Member
Joined
Feb 28, 2012
Messages
535
When you copy my formual, don't copy the { or } characters, they will be included once you hit ctr shift enter.

So just copy this

=IF($A4=1,IFERROR(INDEX(B$3:B$20,SMALL(IF($A$3:$A$20=0,ROW($A$3:$A$20)-ROW($A$3)+1),COUNTIF($A$3:A3,$A$3))),""),"")
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,213
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

The link in post #1 provides no information for me. In any case you will get many more potential helpers if you explain your problem clearly in words and, if needed, post a small screen shot or two directly in your post. My signature block below suggests 3 ways you can do that.

In any case, based on the data in post #2, would this simple non-array formula in G4, copied across and down, suffice?

Excel Workbook
ABCDEFGH
2HG10120130326163553
302530MY1120121231MYR
411DR0.232530
512DR902530
613CR25.882530
714CR0.232530
815CR64.122530
9235104192.73
1002631MY1120121231MYR
11136CR6590.542631
12137DR2339.642631
13138DR170.562631
141192CR497934.482631
151193DR18948.642631
161194CR1421.142631
171195CR236.862631
181196CR17290.642631
192161917287.24
20T201
Fill Values
 

TheCman81

Well-known Member
Joined
Feb 28, 2012
Messages
535
Hi Peter,

Because the OP has specified that there could be anything other than 1 in Col A, your formula would fail if it had 2 or T (as per below example)

Excel 2010
ABCDEFGH
1
2HG10120130326163553
302530MY1120121231MYR
4T1DR0.23
512DR901DR
613CR25.881DR
724CR0.23
815CR64.124CR
9235104192.7

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1

Worksheet Formulas
CellFormula
G4=IF($A4=1,IF($A3=1,G3,B3),"")

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,213
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Hi Peter,

Because the OP has specified that there could be anything other than 1 in Col A, your formula would fail if it had 2 or T (as per below example)
You may very well be right but I make the following observations.

- The link in post #1 does not work for me. Therefore I assumed you had looked at the data and so I used your sample data where the values like 2 and T are at the bottom of a section of 1s. That seems to also agree with what I can see of the data provided by the link in post #3.

- Assuming the 2 and T values can occur anywhere, where did you get the information that the value to be retrieved is the value from the last '0' row as your formula seems to indicate? I'm not suggesting that you are wrong but I can't see any mention of that in the OPs posts.
 

TheCman81

Well-known Member
Joined
Feb 28, 2012
Messages
535
Without a OP actually providing a more detailed sample I assumed the 0 would always be to the left of the values he required.

But you are correct in assuming from the sample data that there would always be a 1, I was merely suggesting that there could be another vaule, but then you could say if there was anything other than a 0 to the left of the return values, my formula would fail.

Hopefully the OP can clarify the above points
 

roth_georg

New Member
Joined
Feb 20, 2008
Messages
37
Hi guys. Thanks a lot for your help. I really would like to provide you the example but I have not found out how I can share a sampe excel file. Maybe you can help?

When I try to enter or copy/paste one of the formulas I get the message "The formula you typed contains an error" although I copied the formula into Excel 2010. When clicking the "Ok"-button "1,IF" or "1,IFERROR" in the formula bar is highlighted.

No idea....could it be that I need to replace "," with ";"?

Thanks for the discussion.
Georg
 

roth_georg

New Member
Joined
Feb 20, 2008
Messages
37
SOLVED!!!! Wrong settings!! I have replaced "," with ";"?

I will check correctness!!

Georg
 

Watch MrExcel Video

Forum statistics

Threads
1,133,525
Messages
5,659,327
Members
418,497
Latest member
VAllen79

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