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

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

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


Excel 2010
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
Sheet1
Cell Formulas
RangeFormula
G4{=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))),""),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.
 

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
55,853
Office Version
  1. 365
Platform
  1. Windows
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
55,853
Office Version
  1. 365
Platform
  1. Windows
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
 

Forum statistics

Threads
1,172,124
Messages
5,879,202
Members
433,409
Latest member
momo2809

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