Paste results consecutively within column (based off other worksheet)

[conorfinnegan]

I have a few worksheets that pull data from another worksheet. In the
third column of the main data is a number from 0 to 5, with many 0's.
What I want to do is run a formula or code on the results page where if
the number from column 3 is greater than 0, it pastes the matching text
from column A on the data sheet into the results sheet without any
spaces. For instance:

Data sheet (example)
Happy 4000 0
Sad 5000 1
Tired 6000 0
Angry 2000 2

What I get on the results page when I run an If formula is

(blank cell)
Sad
(blank cell)
Angry

What formula would I use to make it automatically put angry right under
sad with no blank spaces on the results page? ie...if no match found,
it runs the formula again until match is found

Any help you can give would be greatly appreciated. Thanks

 

[vezerid]

Assuming your data sheet is called 'Data'. Assuming input table is in
'Data'!A2:C20.

In your output sheet, in A2, enter the following *array* formula:

=INDEX(Data!A$2:A$20,MATCH(1,--(Data!$C$2:$C$20>0),0))

Copy this formula across the three columns in cells A2:C2 (or further
down if needed).

In A3 enter the following *array formula*

=IF(ISNUMBER(MATCH(1,(Data!$C$2:$C$20>0)*(COUNTIF(A$2:A2,Data!A$2:A$20)=0),0)),INDEX(Data!A$2:A$20,MATCH(1,(Data!$C$2:$C$20>0)*(COUNTIF(A$2:A2,Data!A$2:A$20)=0),0)),"")

Copy this formula across three columns (or more if needed) and as far
down as necessary.

HTH
Kostis Vezerides

 

[conorfinnegan]

Thanks so much Kostis...It helped me out greatly. Another thing though
if you can help.
I have another list of data not relying on #'s but need it to perform
the similar function.

Lets say I categorized the data into 4 categories...lose, win, tie, not
enough information....

I then wanted to populate the results onto another sheet where those
items under win and tie would be placed together and the other two
would fill other columns consecutively with no spaces. Does this make
sense?

ie.

Blue win
Red tie
Green lose
Grey not enough data

the results sheet

Win/Tie: Lose: Not Enough Data:
Blue Green not enough data
Red

I hope this makes sense. Thanks again for your help. Where did you
learn Excel?

 

[vernalGreens]

COUNTIF(A$2:A2,Data!A$2:A$20)=0
Can you explain this condition in the formula you gave? When will the
value be zero?
How does countif work if the first parameter is a range?

 

[vezerid]

I think I understand what you are looking for, in fact on the same day
I answered to your post I answered a similar post, which was looking
for exactly this. Assuming again your data is in 'Data'!A2:B20. Column
A:A contains the colors, column B:B contains the classification.

In your output sheet I am assuming you are entering the first row
(A1:C1) exactly as it appears in your post, i.e.:
Win/Tie: Lose: Not Enough Data:
In other words, let the two categories in A1 be together, separated
somehow. The formula is based on the assumption that both categories
will appear in the cell.

In A2 (*array formula again*):

=INDEX(Data!$A$2:$A$20,MATCH(1,-
-(ISNUMBER(FIND(Data!$B$2:$B$20,A$1))),0))

Copy this formula through A2:C2

In A3 (*array formula*):

=IF(ISNUMBER(MATCH(1,(ISNUMBER(FIND(Data!$B$2:$B$20,A$1)))*(COUNTIF(A$2:A2,Data!$A$2:$A$20)=0),0)),INDEX(Data!$A$2:$A$20,MATCH(1,(ISNUMBER(FIND(Data!$B$2:$B$20,A$1)))*(COUNTIF(A$2:A2,Data!$A$2:$A$20)=0),0)),"")

Copy across and down. That should do it, if I have understood correctly
what you want to do.

As for where I learned Excel, I am a programmer, who found it easy to
learn the basics of Excel. Then, through the teaching of Business
Computing for several years I augmented my techniques repertoire.
Lately I started following this group more closely and I have learned
even more advanced techniques. The formulas I used here I learned in
this group: In fact I was struggling for a similar formula myself and
had only managed to do it using two columns, until I saw a post by Bob
Phillips, a frequent contributor to these groups, which was extracting
a collection of unique values from a set of data. The formulas you see
here are an adaptation of this formula.

HTH
Kostis Vezerides

 

[vezerid]

The idea of this formula is the following: It tries to find values in a
column, which (a) meet a certain criterion and (b) have not been found
yet. The key to the formula is the virtual array which is used in
MATCH:

MATCH(1,(Data!$C$2:$C$20>0)*(COUNTIF(A$2:A2,Data!A$2:A$20)=0),­0)

We are trying to find the first 1 in an array which will have 1's if
the criterion is met -- in this case (Data!$C$2:$C$20>0) -- AND if the
value is not found ABOVE. Notice that the first formula is different
from the rest. The first formula finds the first element matching the
criterion and populates the first entry of the output. Subsequent
formulas are also asking if it has also been found already.

COUNTIF *always* expects the first argument to be a range. It is in
array formulas that the second argument can ALSO be a range. When we
are in A5 of the output, the expression will have been:

MATCH(1,(Data!$C$2:$C$20>0)*(COUNTIF(A$2:A4,Data!A$2:A$20)=0),­0)
------ Notice the A$2:A4. It is a trick with the $$.

At that point, COUNTIF asks if *each* of the cells in Data!A$2:A$20 is
found above. The result will be a 1/0 for each of the input values,
which is multiplied with a similar array of 1/0 for whether the
criterion is met. Thus, MATCH will first find a 1 in the position where
BOTH the criterion is met AND this record has not already been
retrieved.

Does this help?

Kostis Vezerides