Pull value from column to another cell, formula help

[capex]

I am trying to pull value from column B based on Value in cloumn A, populate column C with value from Column B. I have repeted value in Column A. Example.

Column A Column B Column C
AT 400 400
CT 300 -100
GT 200
AT -100
CT 200
GT 300

I want to populate column C cells with valued from B based on Value AT from Column A. Please help, Thank you in advance.

AD

 

[Peter_SSs]

Here's one way.

C2 & D2 formulas copied down to the end of your data.
Columns D:E could be hidden.

Excel Workbook
	A	B	C	D	E
1	Code	Number	AT	0	5
2	AT	400	400	1
3	CT	300	200	1
4	AT	200	200	2
5	CT	-100	6	2
6	AT	200	8	3
7	CT	300		3
8	AT	6		4
9	CT	5		4
10	GT	3		4
11	AT	8		5
12	CT	9		5
13	GT	4		5
14
Fill Values

 

[capex]

Peter, WOW..., It worked thank you very much for all your help, you are very talented. Thanks. AD

 

[capex]

Hi, Peter,

I got in to little problem please look at this following shot, this is what happens when I choose particular Initial in Column as "AT" but when I pick somebody else who does not have as many value #Value! shows up in column D Cells, see shot 2. When this happens it is distorting my chart. Can you please help, everything else is working. Thanks.

AD

Excel 2007<table rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6" cellpadding="2.5px"><colgroup><col style="background-color: #E0E0F0" width="25px"><col><col><col><col></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th>
</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr><td style="color: #161120;text-align: center;">103</td><td style="text-align: right;;">$628.00 </td><td style="text-align: right;;">$2,886.00 </td><td style="text-align: right;;">$9,189.20 </td><td style="text-align: right;;">21</td></tr><tr><td style="color: #161120;text-align: center;">104</td><td style="text-align: right;;">($336.10)</td><td style="text-align: right;;">$833.00 </td><td style="text-align: right;;">$10,022.20 </td><td style="text-align: right;;">21</td></tr><tr><td style="color: #161120;text-align: center;">105</td><td style="text-align: right;;">($336.10)</td><td style="text-align: right;;">$753.00 </td><td style="text-align: right;;">$10,775.20 </td><td style="text-align: right;;">22</td></tr><tr><td style="color: #161120;text-align: center;">106</td><td style="text-align: right;;">($772.18)</td><td style="text-align: right;;">($2,028.00)</td><td style="text-align: right;;">$8,747.20 </td><td style="text-align: right;;">22</td></tr><tr><td style="color: #161120;text-align: center;">107</td><td style="text-align: right;;">($772.18)</td><td style="text-align: right;;">($2,913.00)</td><td style="text-align: right;;">$5,834.20 </td><td style="text-align: right;;">23</td></tr><tr><td style="color: #161120;text-align: center;">108</td><td style="text-align: right;;">($939.50)</td><td style="text-align: right;;">($2,520.00)</td><td style="text-align: right;;">$3,314.20 </td><td style="text-align: right;;">23</td></tr><tr><td style="color: #161120;text-align: center;">109</td><td style="text-align: right;;">($199.10)</td><td style="text-align: right;;">$2,431.50 </td><td style="text-align: right;;">$5,745.70 </td><td style="text-align: right;;">23</td></tr><tr><td style="color: #161120;text-align: center;">110</td><td style="text-align: right;;">($291.65)</td><td style="text-align: right;;">($1,084.00)</td><td style="text-align: right;;">$4,661.70 </td><td style="text-align: right;;">23</td></tr><tr><td style="color: #161120;text-align: center;">111</td><td style="text-align: right;;">($199.10)</td><td style="text-align: right;;">$2,254.00 </td><td style="text-align: right;;">$6,915.70 </td><td style="text-align: right;;">23</td></tr><tr><td style="color: #161120;text-align: center;">112</td><td style="text-align: right;;">($1,628.50)</td><td style="text-align: right;;">$1,869.00 </td><td style="text-align: right;;">$8,784.70 </td><td style="text-align: right;;">24</td></tr><tr><td style="color: #161120;text-align: center;">113</td><td style="text-align: right;;">($300.44)</td><td style="text-align: right;;">($2,342.00)</td><td style="text-align: right;;">$6,442.70 </td><td style="text-align: right;;">24</td></tr><tr><td style="color: #161120;text-align: center;">114</td><td style="text-align: right;;">($300.44)</td><td style="text-align: right;;">($3,578.00)</td><td style="text-align: right;;">$2,864.70 </td><td style="text-align: right;;">25</td></tr><tr><td style="color: #161120;text-align: center;">115</td><td style="text-align: right;;">$649.74 </td><td style="text-align: right;;">$630.00 </td><td style="text-align: right;;">$3,494.70 </td><td style="text-align: right;;">25</td></tr><tr><td style="color: #161120;text-align: center;">116</td><td style="text-align: right;;">$68.96 </td><td style="text-align: right;;">($979.50)</td><td style="text-align: right;;">$2,515.20 </td><td style="text-align: right;;">25</td></tr><tr><td style="color: #161120;text-align: center;">117</td><td style="text-align: right;;">$234.90 </td><td style="text-align: right;;">$575.50 </td><td style="text-align: right;;">$3,090.70 </td><td style="text-align: right;;">25</td></tr><tr><td style="color: #161120;text-align: center;">118</td><td style="text-align: right;;">$151.93 </td><td style="text-align: right;;">$2,038.00 </td><td style="text-align: right;;">$5,128.70 </td><td style="text-align: right;;">25</td></tr><tr><td style="color: #161120;text-align: center;">119</td><td style="text-align: right;;">$151.93 </td><td style="text-align: right;;">($4,287.00)</td><td style="text-align: right;;">$841.70 </td><td style="text-align: right;;">25</td></tr><tr><td style="color: #161120;text-align: center;">120</td><td style="text-align: right;;">$1,258.00 </td><td style="text-align: right;;">$3,030.44 </td><td style="text-align: right;;">$3,872.14 </td><td style="text-align: right;;">26</td></tr><tr><td style="color: #161120;text-align: center;">121</td><td style="text-align: right;;">$111.72 </td><td style="text-align: right;;">$3,928.00 </td><td style="text-align: right;;">$7,800.14 </td><td style="text-align: right;;">26</td></tr><tr><td style="color: #161120;text-align: center;">122</td><td style="text-align: right;;">$489.93 </td><td style="text-align: right;;">$288.00 </td><td style="text-align: right;;">$8,088.14 </td><td style="text-align: right;;">26</td></tr><tr><td style="color: #161120;text-align: center;">123</td><td style="text-align: right;;">$111.93 </td><td style="text-align: right;;">$289.00 </td><td style="text-align: right;;">$8,377.14 </td><td style="text-align: right;;">26</td></tr><tr><td style="color: #161120;text-align: center;">124</td><td style="text-align: right;;">$111.93 </td><td style="text-align: right;;">$859.00 </td><td style="text-align: right;;">$9,236.14 </td><td style="text-align: right;;">26</td></tr><tr><td style="color: #161120;text-align: center;">125</td><td style="text-align: right;;">$111.93 </td><td style="text-align: right;;">$151.00 </td><td style="text-align: right;;">$9,387.14 </td><td style="text-align: right;;">26</td></tr><tr><td style="color: #161120;text-align: center;">126</td><td style="text-align: right;;">$938.00 </td><td style="text-align: right;;">$224.00 </td><td style="text-align: right;;">$9,611.14 </td><td style="text-align: right;;">27</td></tr><tr><td style="color: #161120;text-align: center;">127</td><td style="text-align: right;;">($325.13)</td><td style="text-align: right;;">$73.00 </td><td style="text-align: right;;">$9,684.14 </td><td style="text-align: right;;">27</td></tr><tr><td style="color: #161120;text-align: center;">128</td><td style="text-align: right;;">($636.25)</td><td style="text-align: right;;">$150.50 </td><td style="text-align: right;;">$9,834.64 </td><td style="text-align: right;;">27</td></tr></tbody></table>

Equity Curve​

<table rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" cellpadding="2.5px" width="85%"><tbody><tr><td style="padding:6px">Worksheet Formulas<table rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6" cellpadding="2.5px" width="100%"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">B103</th><td style="text-align:left">='Data Entry'!AD103</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">C103</th><td style="text-align:left">=IF(ROWS(C$2:C103)>F$1,"",INDEX(B$1:B$1000,MATCH(ROWS(C$2:C103),E$1:E$1000,0)))</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">D103</th><td style="text-align:left">=D102+C103</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">E103</th><td style="text-align:left">=E102+(A103='Performance Analysis'!$J$1)</td></tr></tbody></table></td></tr></tbody></table>
Excel 2007<table rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6" cellpadding="2.5px"><colgroup><col style="background-color: #E0E0F0" width="25px"><col><col><col><col></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th>
</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr><td style="color: #161120;text-align: center;">103</td><td style="text-align: right;;">$628.00 </td><td style="text-align: right;;">$3,030.44 </td><td style="text-align: right;;">$2,230.96 </td><td style="text-align: right;;">21</td></tr><tr><td style="color: #161120;text-align: center;">104</td><td style="text-align: right;;">($336.10)</td><td style="text-align: right;;">$390.00 </td><td style="text-align: right;;">$2,620.96 </td><td style="text-align: right;;">22</td></tr><tr><td style="color: #161120;text-align: center;">105</td><td style="text-align: right;;">($336.10)</td><td style="text-align: right;;">$37.00 </td><td style="text-align: right;;">$2,657.96 </td><td style="text-align: right;;">22</td></tr><tr><td style="color: #161120;text-align: center;">106</td><td style="text-align: right;;">($772.18)</td><td style=";">
</td><td style="text-align: right;;">#VALUE!</td><td style="text-align: right;;">23</td></tr><tr><td style="color: #161120;text-align: center;">107</td><td style="text-align: right;;">($772.18)</td><td style=";">
</td><td style="text-align: right;;">#VALUE!</td><td style="text-align: right;;">23</td></tr><tr><td style="color: #161120;text-align: center;">108</td><td style="text-align: right;;">($939.50)</td><td style=";">
</td><td style="text-align: right;;">#VALUE!</td><td style="text-align: right;;">23</td></tr><tr><td style="color: #161120;text-align: center;">109</td><td style="text-align: right;;">($199.10)</td><td style=";">
</td><td style="text-align: right;;">#VALUE!</td><td style="text-align: right;;">24</td></tr><tr><td style="color: #161120;text-align: center;">110</td><td style="text-align: right;;">($291.65)</td><td style=";">
</td><td style="text-align: right;;">#VALUE!</td><td style="text-align: right;;">24</td></tr><tr><td style="color: #161120;text-align: center;">111</td><td style="text-align: right;;">($199.10)</td><td style=";">
</td><td style="text-align: right;;">#VALUE!</td><td style="text-align: right;;">24</td></tr><tr><td style="color: #161120;text-align: center;">112</td><td style="text-align: right;;">($1,628.50)</td><td style=";">
</td><td style="text-align: right;;">#VALUE!</td><td style="text-align: right;;">24</td></tr><tr><td style="color: #161120;text-align: center;">113</td><td style="text-align: right;;">($300.44)</td><td style=";">
</td><td style="text-align: right;;">#VALUE!</td><td style="text-align: right;;">24</td></tr><tr><td style="color: #161120;text-align: center;">114</td><td style="text-align: right;;">($300.44)</td><td style=";">
</td><td style="text-align: right;;">#VALUE!</td><td style="text-align: right;;">24</td></tr><tr><td style="color: #161120;text-align: center;">115</td><td style="text-align: right;;">$649.74 </td><td style=";">
</td><td style="text-align: right;;">#VALUE!</td><td style="text-align: right;;">24</td></tr><tr><td style="color: #161120;text-align: center;">116</td><td style="text-align: right;;">$68.96 </td><td style=";">
</td><td style="text-align: right;;">#VALUE!</td><td style="text-align: right;;">25</td></tr><tr><td style="color: #161120;text-align: center;">117</td><td style="text-align: right;;">$234.90 </td><td style=";">
</td><td style="text-align: right;;">#VALUE!</td><td style="text-align: right;;">25</td></tr><tr><td style="color: #161120;text-align: center;">118</td><td style="text-align: right;;">$151.93 </td><td style=";">
</td><td style="text-align: right;;">#VALUE!</td><td style="text-align: right;;">25</td></tr><tr><td style="color: #161120;text-align: center;">119</td><td style="text-align: right;;">$151.93 </td><td style=";">
</td><td style="text-align: right;;">#VALUE!</td><td style="text-align: right;;">25</td></tr><tr><td style="color: #161120;text-align: center;">120</td><td style="text-align: right;;">$1,258.00 </td><td style=";">
</td><td style="text-align: right;;">#VALUE!</td><td style="text-align: right;;">25</td></tr><tr><td style="color: #161120;text-align: center;">121</td><td style="text-align: right;;">$111.72 </td><td style=";">
</td><td style="text-align: right;;">#VALUE!</td><td style="text-align: right;;">25</td></tr><tr><td style="color: #161120;text-align: center;">122</td><td style="text-align: right;;">$489.93 </td><td style=";">
</td><td style="text-align: right;;">#VALUE!</td><td style="text-align: right;;">25</td></tr><tr><td style="color: #161120;text-align: center;">123</td><td style="text-align: right;;">$111.93 </td><td style=";">
</td><td style="text-align: right;;">#VALUE!</td><td style="text-align: right;;">26</td></tr><tr><td style="color: #161120;text-align: center;">124</td><td style="text-align: right;;">$111.93 </td><td style=";">
</td><td style="text-align: right;;">#VALUE!</td><td style="text-align: right;;">26</td></tr><tr><td style="color: #161120;text-align: center;">125</td><td style="text-align: right;;">$111.93 </td><td style=";">
</td><td style="text-align: right;;">#VALUE!</td><td style="text-align: right;;">26</td></tr><tr><td style="color: #161120;text-align: center;">126</td><td style="text-align: right;;">$938.00 </td><td style=";">
</td><td style="text-align: right;;">#VALUE!</td><td style="text-align: right;;">26</td></tr><tr><td style="color: #161120;text-align: center;">127</td><td style="text-align: right;;">($325.13)</td><td style=";">
</td><td style="text-align: right;;">#VALUE!</td><td style="text-align: right;;">27</td></tr><tr><td style="color: #161120;text-align: center;">128</td><td style="text-align: right;;">($636.25)</td><td style=";">
</td><td style="text-align: right;;">#VALUE!</td><td style="text-align: right;;">27</td></tr></tbody></table>

Equity Curve​

<table rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" cellpadding="2.5px" width="85%"><tbody><tr><td style="padding:6px">Worksheet Formulas<table rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6" cellpadding="2.5px" width="100%"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">B103</th><td style="text-align:left">='Data Entry'!AD103</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">C103</th><td style="text-align:left">=IF(ROWS(C$2:C103)>F$1,"",INDEX(B$1:B$1000,MATCH(ROWS(C$2:C103),E$1:E$1000,0)))</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">D103</th><td style="text-align:left">=D102+C103</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">E103</th><td style="text-align:left">=E102+(A103='Performance Analysis'!$J$1)</td></tr></tbody></table></td></tr></tbody></table>

 

[Peter_SSs]

What do you want returned in those #VALUE! cells? For example, what should be in cell D106 of your second screen shot?

Taking a guess, see if this helps. (This would be for row 103 per the formulas posted in your post.)

=IF(C103="","",D102+C103)

 

[capex]

Cells in column C gets populated depending on how many entry that client has, some clients have longer columns compare to other so length of column C expands or shrinks. My chart parameters are set C2:C128 but some falls short and ends at 105 0r 106 as seen in shot 2. I have ended up using following formula which returns #N/A and it works for chart, If you have any other idea please let me know. You have been very helpful, thank you.

D2=IF(ISERROR(D1+C2),NA(), D1+C2)

Thanks, AD.

 

[Peter_SSs]

You could probably simplify that a bit to

=IF(C2="",NA(), D1+C2)

 

[capex]

Thank you, worked perfectly. AD