Simple Formula help......................

[sssb2000]

Hi,
in A, i have this information:

Investment 1
Investment 2
Investment 3
Investment 4
Investment 5
Investment 3
Investment 4
Investment 5
Investment 2
Investment 2
Investment 2
Investment 2

in B, i have numbers for each investment.

the content of A is expanding and shrinking and is random.
in C1, i will have an investment name.
In D:E, i'd like all the investments and their numbers to show in order....and only the information for the C1 investment.

for example, if C1 has "investment 2", D:E would have 5 rows saying Investment 2 and the number for each (because there are 5 mentions of investment 2 in A)

how do i do this with a formula?

 

[JonesyUK]

2 quick ways:
A)
1) create a pivot table with the data, i.e. select A1:B100 (or whatever row u need)
2) u can place the pivot table right next to the data
Assume Column A is "Name" and Column B is "Amount"
3) Report Filter = Name
4) Row Labels = Amount
5) Values = Sum of Amount

You can now use the dropdown to select any investment

B) Used the Advanced Filter in Excel

Both methods will do it....

Not sure about a formula for this sort of thing....

 

[sssb2000]

thanks for the response.
this does not do what i need.

Because my data is automatically inputted in C1, without any 'selection from the user', i will need a formula that does what i described

 

[JonesyUK]

okay, I've got this to work, but you need an extra step.... I will send the details for the download link in a private message....

Let me know if you've got it....

Thanks,

 

[sssb2000]

okay, I've got this to work, but you need an extra step.... I will send the details for the download link in a private message....

Let me know if you've got it....

Thanks,

Wow, thank you so much.

it works great. Is there any way i can get rid of the "N/A"s?

 

[JonesyUK]

try the new file that I uploaded. Simply used Conditional formatting, but may not work with excel 2003... not sure....

 

[sssb2000]

try the new file that I uploaded. Simply used Conditional formatting, but may not work with excel 2003... not sure....

thank you again for your help.
The formula in E2 is:
{=INDEX($A$2:$C$1000,(MATCH(0,COUNTIF($E$1:$E1,$A$2:$A$1000)+($B$2:$B$1000<>$D$1),0)),COLUMN(A1))}

how could i modify the formula in E2 when my column A has been shifted 3 columns to the right to C?
in other words, my $A$2:$C$1000 is now $C$5:$E$300