how to add individual value in output column and summation

[trustmeasfrnd]

hi,
how can i use formula by which when i input above valued it get in output Like for
MH1 in A1 its value 40 under A1 and 32 under A2 and 44 under A3. similarly for MH2 and so on but all Mh1 MH2 are variables.if there is no 2nd or 3rd Value it should show zero in A2 and A3.

i know its very easy but i am not able to do it using formula i am not sure how to vary range if i use index function.

Book1
	A	B	C	D	E	F	G
1	Col A	Col B	COL C
2	MH1	65	40
3	MH1	60	32
4	MH1	40	44
5	MH2	100	60
6	MH2	80	48
7	MH3	90	54
8	MH4	110	66
9	MH4	125	75
10	MH5	55	33
11	MH5	75	45
12	MH5	85	51
13	MH6	88	52.8
14	MH7	69	41.4
15	MH7	112	67.2
16
17
18	OUTPUT
19	S. No.		A1	A2	A3	Summation
20	1	MH1				0
21	2	MH2				0
22	3	MH3				0
23	4	MH4				0
24	5	MH5				0
25	6	MH6				0
26	7	MH7				0
27
Sheet4

 

[oldbrewer]

item	value1	value2	helpcol1								list of row numbers
MH1	65	40	1										2
MH1	60	32											3
MH1	40	44											4
MH2	100	60	2										5
MH2	80	48											6
MH3	90	54	3										7
MH4	110	66	4										8
MH4	125	75											9
MH5	55	33	5										10
MH5	75	45											11
MH5	85	51											12
MH6	88	52.8	6										13
MH7	69	41.4	7										14
MH7	112	67.2											15
OUTPUT		HELPER TABLE							helpcol1	increases the counter every time col A changes
S. No.
1	MH1	2	3	4					helper table	the first 2 got by
2	MH2	5	6						=OFFSET($D$1,MATCH(SMALL($D$2:$D$15,A20),$D$2:$D$15,0),10)
3	MH3	7
4	MH4	8	9						the 3 to the right of it got by
5	MH5	10	11	12					=IF($C21="",IF(C20+1<=MAX($N$2:$N$15),C20+1,""),IF(C20="","",IF(C20+1<$C21,C20+1,"")))
6	MH6	13
7	MH7	14	15
									second helper table
									C2 got by	=IF(C20<>"","C"&C20,"")
									ie concatenating "C" plus what is in helper table
OUTPUT		second helper table						OUTPUT		final desired output
S. No.									S. No.
1	MH1	C2	C3	C4					1	MH1	40	32	44
2	MH2	C5	C6						2	MH2	60	48
3	MH3	C7							3	MH3	54
4	MH4	C8	C9						4	MH4	66	75
5	MH5	C10	C11	C12					5	MH5	33	45	51
6	MH6	C13							6	MH6	52.8
7	MH7	C14	C15						7	MH7	41.4	67.2
									the 40 obtained by
									=IF(C34="","",INDIRECT(C34))

<colgroup><col span="5"><col><col span="3"><col><col span="9"></colgroup><tbody>
</tbody>

 

[trustmeasfrnd]

thanks oldbrewer,
it worked nicely.i think i have just to change 10 to 1.

=OFFSET($D$1,MATCH(SMALL($D$2:$D$15,A20),$D$2:$D$15,0),10)
for row i used = Row()
but i could not automate Helpcolumn1.i have to put it manually.
can it be done in more compact way?
thank you for the help.
also when i tried to add the output say for MH2 it gives #value . how to solve that.

 

[oldbrewer]

10 is there to read the column 2......15

item	value1	value2	helpcol1								list of row numbers
MH1	65	40	1										2
MH1	60	32											3
MH1	40	44					the cell below 1 in helpcol1 is				4
MH2	100	60	2				=IF(A3<>A2,MAX($D$1:D2)+1,"")				5
MH2	80	48											6
MH3	90	54	3										7

<colgroup><col span="5"><col><col span="3"><col><col span="5"></colgroup><tbody>
</tbody>

 

[trustmeasfrnd]

hi Oldbrewer,
yeah i also realized that it can be done using above formula.
just a clarification with cells wherever i am using this formula =IF(C34="","",INDIRECT(C34))
can these cells be added algebrically?

thank you.
regards,
Abhisekh

 

[oldbrewer]

why not because it returns a value - if it returned text no....