Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Identify condition using multiple criteria WITHOUT sumproduct?..
Hi all -
Not sure if this is possible but... I have data that looks like 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 | I use a helper column to consolidate the total value of a single ID on the 1st row that ID is found (using UNIQUE ID). So, in the example above there's a column called "Helper: Total ID Value" w/ the 1st row equal to $9, 2nd row $0, 3rd $0, 4th $6, 5th $0. If a record (row) has a type of "BLUE" I want to set a flag on the 1st row an ID appears in a new helper column. So, using the data above this new column would have Flag = true on the 1st and 4th rows. The basic logic I'm looking to implement is something like: Using a given UNIQUE ID, search through all records with a matching ID and if any have "BLUE" as a TYPE, return TRUE. Here's the kicker: I can't use SUMPRODUCT because I have 15K rows and I suspect my calculation times would become totally unmanageable. Make sense? Many thanks for any tips, hints, or condolences you share! -meat |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Identify condition using multiple criteria WITHOUT sumproduct?..
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) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Identify condition using multiple criteria WITHOUT sumproduct?..
If a record (row) has a type of "BLUE" I want to
set a flag on the 1st row an ID appears in a new helper column. So, using the data above this new column would have Flag = true on the 1st and 4th rows. Why would row 1 get flagged? There is no type BLUE on row 1. Based on my understanding of what you want the only row that meets the condition is row 4. -- Biff Microsoft Excel MVP "MeatLightning" wrote in message ... Hi all - Not sure if this is possible but... I have data that looks like 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 | I use a helper column to consolidate the total value of a single ID on the 1st row that ID is found (using UNIQUE ID). So, in the example above there's a column called "Helper: Total ID Value" w/ the 1st row equal to $9, 2nd row $0, 3rd $0, 4th $6, 5th $0. If a record (row) has a type of "BLUE" I want to set a flag on the 1st row an ID appears in a new helper column. So, using the data above this new column would have Flag = true on the 1st and 4th rows. The basic logic I'm looking to implement is something like: Using a given UNIQUE ID, search through all records with a matching ID and if any have "BLUE" as a TYPE, return TRUE. Here's the kicker: I can't use SUMPRODUCT because I have 15K rows and I suspect my calculation times would become totally unmanageable. Make sense? Many thanks for any tips, hints, or condolences you share! -meat |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Identify condition using multiple criteria WITHOUT sumproduct?
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) |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Identify condition using multiple criteria WITHOUT sumproduct?
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) |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Identify condition using multiple criteria WITHOUT sumproduct?..
Have your considered using pivot tables to do your aggregations. They have
minimal overhaed for file size and almost no calculation overhead. They will allow you to aggregate your source data in whatever manner you want as a completely seperate table. -- HTH... Jim Thomlinson "MeatLightning" wrote: Hi all - Not sure if this is possible but... I have data that looks like 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 | I use a helper column to consolidate the total value of a single ID on the 1st row that ID is found (using UNIQUE ID). So, in the example above there's a column called "Helper: Total ID Value" w/ the 1st row equal to $9, 2nd row $0, 3rd $0, 4th $6, 5th $0. If a record (row) has a type of "BLUE" I want to set a flag on the 1st row an ID appears in a new helper column. So, using the data above this new column would have Flag = true on the 1st and 4th rows. The basic logic I'm looking to implement is something like: Using a given UNIQUE ID, search through all records with a matching ID and if any have "BLUE" as a TYPE, return TRUE. Here's the kicker: I can't use SUMPRODUCT because I have 15K rows and I suspect my calculation times would become totally unmanageable. Make sense? Many thanks for any tips, hints, or condolences you share! -meat |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Identify condition using multiple criteria WITHOUT sumproduct?
Ah, yes... the Pivot Table... they scare the me sweaty. Um, I guess if I'm
totally S.O.L. I'll take another look? |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Identify condition using multiple criteria WITHOUT sumproduct?
Nothing to be afraid of... they are really quite easy...
On the sheet with your source data select Data - Pivot Table... When the wizard opens just choose finish (99.9% of the time XL will guess correctly) A new sheet will open with a pivot table and a field list Drag the Value to the center data area Drag the Unique Id to the Left hand column. Drag the Type to the left hand column. Each of the fields on the pivot table can be dragged anywhere else in the table that you want... That's the basics. -- HTH... Jim Thomlinson "MeatLightning" wrote: Ah, yes... the Pivot Table... they scare the me sweaty. Um, I guess if I'm totally S.O.L. I'll take another look? |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Identify condition using multiple criteria WITHOUT sumproduct?
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. |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Identify condition using multiple criteria WITHOUT sumproduct?
Cool. Thanks! Really appreciate the help.
This issue I'm trying to solve is actually a small part of a much bigger system... lots of workbooks with inter-dependencies, etc... so I'm worried that flipping to a pivot table approach will force a re-write of the whole thing.... which I'd like to avoid if at all possible. |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
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) |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Identify condition using multiple criteria WITHOUT sumproduct?
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. |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Identify condition using multiple criteria WITHOUT sumproduct?
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. |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
Identify condition using multiple criteria WITHOUT sumproduct?
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 |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
Identify condition using multiple criteria WITHOUT sumproduct?
Anyway, I get the feeling this is a lost cause...
Nah, not a problem! Let's assume the raw data is in the range A2:D6 (A1:D1 = 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:D$6="blue"))0) For "Is Yellow" entered in H2: =IF(F2="-","-",SUMPRODUCT(--(A$2:A$6=F2),--(D$2:D$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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple Criteria for Sumproduct | Excel Worksheet Functions | |||
SUMPRODUCT / SUMIF on Multiple Worksheets with Multiple Criteria | Excel Discussion (Misc queries) | |||
Multiple Lookup as condition in sumproduct formula | Excel Worksheet Functions | |||
Identify Duplicate Items Based On Multiple Criteria | Excel Worksheet Functions | |||
Using Sumproduct with multiple Criteria | Excel Worksheet Functions |