Totally lost - Greenhorn question --> filling cells

[roth_georg]

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>

 

[TheCman81]

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
	A	B	C	D	E	F	G	H
2	H	G101	20130326	163553
3	0	25	30	MY11	20121231	MYR
4	1	1	DR		0.23		25	30
5	1	2	DR		90		25	30
6	1	3	CR		25.88		25	30
7	1	4	CR		0.23		25	30
8	1	5	CR		64.12		25	30
9	2	35	104192.73
10	0	26	31	MY11	20121231	MYR
11	1	36	CR		6590.54		26	31
12	1	37	DR		2339.64		26	31
13	1	38	DR		170.56		26	31
14	1	192	CR		497934.48		26	31
15	1	193	DR		18948.64		26	31
16	1	194	CR		1421.14		26	31
17	1	195	CR		236.86		26	31
18	1	196	CR		17290.64		26	31
19	2	161	917287.24
20	T	201
Sheet1

Cell Formulas
Range	Formula
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.

 

[roth_georg]

Hi, this is incredible!

But somehow using Ctrl+Shift+Enter does not work. If I use a simple array formula like =</STRONG>SUM(</STRONG>C2:C11*D2:D11)it perfectly works.</STRONG> Not sure what I am doing wrong.

https://docs.google.com/file/d/0B198lEjBCQpndDYyMHpFR0ZiaVU/edit?usp=sharing

Really not sure what's wrong!

Thanks for the help. Much appreciated.
Georg
georg.roth(at)hotmail.com

 

[TheCman81]

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]

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
	A	B	C	D	E	F	G	H
2	H	G101	20130326	163553
3	0	25	30	MY11	20121231	MYR
4	1	1	DR		0.23		25	30
5	1	2	DR		90		25	30
6	1	3	CR		25.88		25	30
7	1	4	CR		0.23		25	30
8	1	5	CR		64.12		25	30
9	2	35	104192.73
10	0	26	31	MY11	20121231	MYR
11	1	36	CR		6590.54		26	31
12	1	37	DR		2339.64		26	31
13	1	38	DR		170.56		26	31
14	1	192	CR		497934.48		26	31
15	1	193	DR		18948.64		26	31
16	1	194	CR		1421.14		26	31
17	1	195	CR		236.86		26	31
18	1	196	CR		17290.64		26	31
19	2	161	917287.24
20	T	201
Fill Values

 

[TheCman81]

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

	A	B	C	D	E	F	G	H
1
2	H	G101	20130326	163553
3	0	25	30	MY11	20121231	MYR
4	T	1	DR		0.23
5	1	2	DR		90		1	DR
6	1	3	CR		25.88		1	DR
7	2	4	CR		0.23
8	1	5	CR		64.12		4	CR
9	2	35	104192.7

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

Sheet1​

Worksheet Formulas Cell Formula G4 =IF($A4=1,IF($A3=1,G3,B3),"") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[Peter_SSs]

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]

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]

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]

SOLVED!!!! Wrong settings!! I have replaced "," with ";"?

I will check correctness!!

Georg