Xcel97

[Rudi]

Can't find a solution to the following:
Column "A"=categories (1-5). Column "B"=Curency. The
amounts from column "B" should be automatically
transferred to Columns "C-G" according to the category
indicated in column "A". Puting data in cells starting
with the top cell.

A solution would be appreciated.
Rudi

 

[Anon]

Can't find a solution to the following:
Column "A"=categories (1-5). Column "B"=Curency. The
amounts from column "B" should be automatically
transferred to Columns "C-G" according to the category
indicated in column "A". Puting data in cells starting
with the top cell.

A solution would be appreciated.
Rudi

Formula for C1:
=IF(A1=1,B1,"")
Formula for D1:
=IF(A1=2,B1,"")
etc.
Then copy C1:G1 down as far as required.

 

[Rudi]

-----Original Message-----


Formula for C1:
=IF(A1=1,B1,"")
Formula for D1:
=IF(A1=2,B1,"")
etc.
Then copy C1:G1 down as far as required.

Thank you for your quick reply. However I am familiar

with IF functions. The problem is that the results of the
function remain on the same line as the original data,
leaving empty cells where the data goes to a diferent
column. How can I eliminate the empty cells inbetween
those that contain data.
Thank you

 

[Anon]

with IF functions. The problem is that the results of the
function remain on the same line as the original data,
leaving empty cells where the data goes to a diferent
column. How can I eliminate the empty cells inbetween
those that contain data.
Thank you

Sorry, I completely misunderstood what you were asking for.
It would be easier simply to sort your original data by category. Then 5
simple copy/pastes would achieve what you want.

 

[Rudi]

-----Original Message-----

Sorry, I completely misunderstood what you were asking for.
It would be easier simply to sort your original data by category. Then 5
simple copy/pastes would achieve what you want.


.I agree that it would be simple to do what you propose.

However I recall that there is a function that fills cells
from the top of a column, not leaving any blanks. My
problem is I don't even remember the name for the function
to be able to find it in help. It is a bit similar
to "concatinate" but in a diferent context.

 

[Rudi]

-----Original Message-----
You'll still need to use the solution Anon provided to
create unique lists. This formula can then be used to
remove the blanks in column C. It's array entered in
another column (Ctrl+Shift+Enter instead of just Enter).

{=IF(ROW()-ROW(C1:Cx)+1>ROWS(C1:Cx)-COUNTBLANK
(C1:Cx),"",INDIRECT(ADDRESS(SMALL((IF(C1:Cx<>"",ROW
(C1:Cx),ROW()+ROWS(C1:Cx))),ROW()-ROW(C1:Cx)+1),COLUMN
(C1:Cx),4)))}

Of course if you need the final results in columns C to G,
then you'll have to move Anon's formulas past column G and
adjust the above to match.



.

 

[Leo Heuser]

Rudi

Here's another option:

Assuming categories in A2:A15,
currency in B2:B15 and
headings 1,2,3,4,5 in C1:G1, enter
this formula in C2:

=SUMPRODUCT((C$1=$A$2:$A$15)*(COUNTIF(INDIRECT("A2:A"&
ROW($A$2:$A$15)),C$1)=ROW()-ROW($C$2)+1)*$B$2:$B$15)

Drag C2 to G2 with the fill handle (the little square in the lower right
corner of the cell)

While C2:G2 is selected, drag the selection down with the fill handle.


--
Best Regards
Leo Heuser
MVP Excel

Followup to newsgroup only please.