Identify condition using multiple criteria WITHOUT sumproduct?..

I do hope you have a good anti-virus program.
I'm supporting MS Communities - in my country - for 5 years and never ever
got infected.
However, even a picture of the source/raw data is much better and more
understanding than many of the question present here.
This is a good site to begin with and I would spare even more 2-3 minutes to
present BOTH: an uploaded WB and a picture.
http://www.imageshack.us
*** Let every participant/supporter decide what suits him best ***
Micky


"Jim Thomlinson" wrote:

Generally speaking most of us who answer questions will not open unknown
files. As per Chips Pearson's website and tips for new posters...

Don't post attachment files. While it may be so that an attachment can
clarify a question, we don't know who you are and thus we cannot trust that
your attached code won't start deleting all the files on the hard drive.

http://www.cpearson.com/excel/HintsA...roupUsers.aspx
--
HTH...

Jim Thomlinson


"מיכ�ל (מיקי) �בידן" wrote:

I'm sure MS know what they are doing.
Did anybody, here, considered to upload his/her WorkBook to a File-Hosting
site - and to let us have the link to that file - in order to prevent himself
from the "torture" of presenting a table like yours ?
If the answer in NO(!) - then I really think you might start thinking that
direction...
Here is one of many such sites:
http://www.yousendit.com
Micky



"MeatLightning" wrote:

Yikes... my sample data got all sorts of messed up... let's try this:

ID | UNIQUE ID | LINE | VALUE | TYPE |
01 | 01 | 1 | $1 | YELLOW |
01 | - | 3 | $5 | RED |
01 | - | 2 | $3 | BLUE |
04 | 04 | 2 | $5 | RED |
04 | - | 1 | $1 | BLUE |

MS should use stackexchange (www.stackexchange.com)

Sorry, I don't understand.

--
Biff
Microsoft Excel MVP


"MeatLightning" wrote in message
...
Yeah, that's part of what makes it hard / weird / impossible?

Basically, I'm consolidating data. My raw data (rows) have multiple
entries... I use helper columns to identify attributes and assign them to
a
single record (row).

So... ID 1 has rows that indicate RED, YELLOW, & BLUE "TYPES"... I want to
have columns for each that say essentially IS RED? (T/F), IS BLUE? (T/F),
etc.

The goal being that the 1st instance of a given ID (hence the "UNIQUE ID"
column) has all the attributes of that ID listed out horizontally instead
of
vertically.

Yeah, it's a tricky one...

I have what I consider "raw" data on the left... using the example data:

ID | LINE | VALUE | TYPE |
01 | 1 | $1 | YELLOW|
01 | 3 | $5 | RED |
01 | 2 | $3 | BLUE |
04 | 2 | $5 | RED |
04 | 1 | $1 | BLUE |

a couple of columns to the right I have "helper" columns that consolidate
the data for me... for example:

UNIQUE ID | IS BLUE | IS YELLOW | TOTAL |
01 | TRUE | TRUE | $9 |
- | - | - | - |
- | - | - | - |
04 | TRUE | FALSE | $6 |
- | - | - | - |

I calculate the "UNIQUE ID" column with this formula
=IF(COUNTIF($A$1:A1,A1)=1,A1,0)
(where "A" is the "ID" column. Note the "$" locks to accommodate a copy &
paste down)

I calculate the "TOTAL" column with this:
=IF(Q1="-",0,SUMIF(Q:Q,Q1,C:C))
(where "Q" is the "UNIQUE ID" column)

What I'm stuck on is how to calculate the "IS BLUE" & "IS YELLOW" columns...

"IS BLUE" column should be "TRUE" when the UNIQUE ID is not "-" AND any of
the rows with a matching "ID" have "BLUE" in the "TYPE" column.

Crap... super awesome idea to limit the line length of a post... *sigh*

The "Helper" example data should be:

U. ID | IS B | IS Y | TOTAL |
01 | T | T | $9 |
- | - | - | - |
- | - | - | - |
04 | T | F | $6 |
- | - | - | - |

(I shortened the column names & true / false data to avoid line breaks)

Also, I messed up the "U. ID" calculation slightly... it should be:
=IF(COUNTIF($A$1:A1,A1)=1,A1,"-")

Anyway, I get the feeling this is a lost cause... but thanks anyway!
-meat

Anyway, I get the feeling this is a lost cause...

Nah, not a problem!

Let's assume the raw data is in the range A26 (A11 = column headers)

The "helper" data is in the range F2:I6 (F1:I1 = column headers)

For "Is Blue" entered in G2:

=IF(F2="-","-",SUMPRODUCT(--(A$2:A$6=F2),--(D$2$6="blue"))0)

For "Is Yellow" entered in H2:

=IF(F2="-","-",SUMPRODUCT(--(A$2:A$6=F2),--(D$2$6="yellow"))0)

Copy down as needed.

--
Biff
Microsoft Excel MVP


"MeatLightning" wrote in message
...
Crap... super awesome idea to limit the line length of a post... *sigh*

The "Helper" example data should be:

U. ID | IS B | IS Y | TOTAL |
01 | T | T | $9 |
- | - | - | - |
- | - | - | - |
04 | T | F | $6 |
- | - | - | - |

(I shortened the column names & true / false data to avoid line breaks)

Also, I messed up the "U. ID" calculation slightly... it should be:
=IF(COUNTIF($A$1:A1,A1)=1,A1,"-")

Anyway, I get the feeling this is a lost cause... but thanks anyway!
-meat