Filter Data By Different Groups Of Values

May 16, 2014

I am trying to improve a spreadsheet that I have to use everyday. I have a list with varying number of entries (different length every day) that has a number of columns including a column that contains machine codes (say 1,2,3,4,5...).

Each machine is programmed by a different person (say Andy,Ben,Craig,etc.).

One programmer can program more than one machine so for instance Andy is interested in programs for machines 1,3,5 and Ben in machines 2 and 4.

Every day we send a list of jobs that require programming which is pre-filtered for each programmer so if I send the list to Andy he only sees rows of data with machine codes 1,3 and 5.

I am trying to make a drop-down where I pick the programmer's name and the list automatically gets filtered for the machines for that specific person (e.g. 1,3,5).

I believe this could be done with advanced filtering but the problem is that the list length changes every day and the data is pasted into the spreadsheet from a different CSV file so I cannot define a set range for advanced filtering because it does not accept blank rows...

I am attaching a screenshot : list.jpg

View 1 Replies


Filter Data Into Groups That Contain A Common Data Point Using Pivot Tables?

Apr 30, 2013

I want to use a Pivot table to filter data to show just the studies that contain patients from the 'South' area?

As per example below I want to be able to see all the patients in all areas but only for studies that have patients from the south. I put together an array formula that works well for small tables but is too much with one one my sheets that contains 200,000 rows.

Before filtering:

Study ID
Study Short Title
Study Patient ID



View 8 Replies View Related

Filter Data Based On Values Located On Another Worksheet

Dec 28, 2011

On a first sheet, I have a list of cities where physicians travel along with an autofilter. On that same worksheet, I have a cell where users can input a zipcode.

On a second (hidden) sheet, a function calculates and displays in column F the distance in miles between the user-input zip code and all the cities in 4 states. The cities are listed in column D.

I need to filter all the cities on the first sheet (the ones where the docs travel) based on their distance from the user-input zipcode - namely all the cities located less than 150 miles from that zipcode.

View 4 Replies View Related

Summarize Groups Of Values

Feb 4, 2009

I have a spreadsheet containing Date, Description and Value in a three column setup.
I wish to generate one summary for each unique description, based on a table of types. I can then say that for instance "Apple", "Oranges", "Lemon" are all part of a "Fruit" group.

The lookup would then find every row containing words in this group and summarize the values for them all.

View 7 Replies View Related

Conditional Formatting For Groups Of Values

Feb 2, 2010

Is there a way to do the following using conditional formatting?

I need to have all duplicates highlighted.

For example (Since "Ana M." is the first) highlight both rows (or at least the group of "Ana M." cells in the column, entire row not necessary)

Ana M. 12 A St 333-3333
Ana M. 23 Z St 333-3333

Jane S. 12 A St 555-5555
Jane S. 15 Z St 555-5555

Sam A. 55 A St 222-2222

Tony J. 45 A St 444-4444
Tony J. 11 B St 444-4444
Tony J. 66 Z St 444-4444

View 9 Replies View Related

Finding Unique Groups Of Values In Columns?

Dec 17, 2013

I have a file with immunophenotypic markers and their relationship to certain blood cells.

What I need to do is search the list and pull out the groups of defining markers for each blood cell. Some of the cells have many markers, some only have one but I need to find which combinations of markers identify each cell if possible.

I have attached the file (I think!)

View 14 Replies View Related

Separating A Range Of Values Into Equal Sum Groups?

Feb 27, 2014

Is it possible to separate a range of cells with numerical values into "n" groups that have equal (or as close to equal as possible) total sums?

Ex. The range A1:A30 includes 30 random numbers between 1 and 12 (obviously there are duplicates). I need excel to auto-generate 6 groups of 5 cells each with each group having the same (again, as close as possible) total sum. Preferably, I'd like the numbers in each group to be similar from top to bottom, but I'm not overly concerned about that.

View 2 Replies View Related

Dividing List Into Equal Groups Determined By Two Values?

Mar 10, 2014

I need to distribute a list of data into 10 even groups of data. Here is the scenario.

I have 10 salespeople on my team with a list of accounts that need to be evenly distributed based on two criteria

- Each sales person needs to have the same number of zipcodes , with the closest TOTAL # of accounts and TOTAL $ value as possible

View 6 Replies View Related

Identify Values Specific To Defined Groups Of Columns?

Sep 2, 2013

I have these kind of matrices (below) and I'd like to identify unique values specific to different groups of columns:

[Code] .........

For instance, in the example above, if we decide that A, B, C are "Group 1" and D, E, F are "Group 2", and that the values in rows 1, 2, 3 are independent (i.e., "x" in "1" is not comparable to "x" in "2", etc):
- In condition (row) 1: "x" is a specific value only found in Group 1
- In condition 1: "y" is a specific value only found in Group 2
- In condition 2: "x" is a value found in majority in Group 1
- In condition 3: no specific value can be associated to Group 1 or 2.

What I would like to get is a measure of whether:
(1) there are values over-represented in one of the groups, or 100% specific to one group.
(2) what are these values
(3) if multiple values are a bit tricky, then: what is the value which is the most over-represented in one group compared to the other (the maximum being 100% in one group and 0% in the other)

View 1 Replies View Related

Use Combobox ListFillRange To List Values From Two Groups Of Cells?

Feb 24, 2010

Is it possible to use the Combobox ListFillRange to list values from two groups of cells? For example, in column 'A', I have the values, 1,2,3; and in Column 'B', I have the values, A, B, C. I want to list the values in column 'A' along with the value of say, B, in Column 'B'.

combobox.listfillrange = "Sheet1!A1:A3" < ---- how to include value B from column B in this list? Or, how to get value A from column B?

View 8 Replies View Related

Filter Only Columns A-F When Apply Filter Values?

Mar 26, 2013

I have some columns on which I have a filter, with some columns next to those that have information in them.

What I need to do is filter only columns A-F when apply filter values, but keep columns I-K fixed as A-F change when they are filtered..

View 1 Replies View Related

Excel 2010 :: Calculate Most Common Text Values By Groups

Mar 14, 2013

I want to calculate the most common text value for groups. I have a table with a lot of records like an example below.

ID Code
1 HB
1 KU
1 KS
1 LV
1 KS
2 LM
2 KS


I want a table like below:

ID Code
1 KU
2 KS
3 KS

How can I do it in Excel 2010?

View 9 Replies View Related

Excel 2013 :: Filter Data And Edit With A Search Instead Of The Filter Button

Oct 5, 2013

I have a database in Excel 2013 and now I want that when a value (a person's name) is entered in a cell. That then the database sort of filters the list for me, so it's still possible to make changes in the entries.


Picture above to specify the search, which I would therefore like to edit

Dashboard_Action Pool Team 7.2.xlsm

I have been all morning working on a simplified version of the tutorial from YouTube: Create your own Excel Search Pt. 4. But came back later so only then that I can not change the data:?

View 2 Replies View Related

How To Use Median On Groups Of Data

Oct 29, 2007

Attached is a spreadsheet showing various stockcodes in various districts with their prices. My aim is to find the median stock price of each stockcode which might show me the district which has a huge variance in price with the same stock. Average can be used on a subtotal, but median is not there. The spreadsheet is an example and the real list is thousands of rows, so I can't manually go and change the range for each stockcode encountered to find the median for each stockcode.

Any suggestions, I am using excel 2003 and can't download add-ins due to my work's network security, though I could do it at home if someone found a good add-in etc.

View 8 Replies View Related

VBA To Get Data Sorted Into Groups

Sep 20, 2013

Explaination of Data:

I have 2 Columns in the "RawData" sheet in the file attached. One says the Campaign No, and the other says the Site Code. One Campaign, will always never have duplicate Site Code.

I want to organize the data in the following fashion (as seen in the "Format" Sheet). I wish to acquire the trends of grouping of Site Codes, meaning which site (by Site code) is sold together, to understand the hottest combinations.

The idea is to see which Site Code sells more with a particular Campaign.

As you can see in the attached file, I'd like to know how many times a particular Sites (by Site Code) was sold with the other. As you can imagine, I have about 300 Campaigns, but have about 1500 different Sites codes to deal with, this activity will save me hours of time.

The sample file is uploaded on Google Docs. [URL] .........

View 1 Replies View Related

Subtotal Groups Of Data

Feb 25, 2007

I have attached sample of a series of massive spreadsheets that I am working on.

Item Numbers in Column A
Flag in Column D
Values in Column P

I want put the total of the values in P for each group of item numbers in column C where the flag is YES.

Unfortuantely I prefer not to use excel's subtotal functionality or array formulaes here because of the logistics of how these are used.

View 9 Replies View Related

Copy Groups Of Data

Apr 22, 2008

I have this an excel file which contains about 500-800 entries. These entries have about seven 0's which denotes where the data starts for a each group. In order to separate the data, I want to copy and paste this data to other workbooks. What would be the best way to select the rows from one 0 to the next?

View 3 Replies View Related

Conditional Formatting Groups Of Data

May 28, 2014

I have a spreadsheet with data in a table with order numbers in column A. Although each order is given a unique number, the data populates with multiple rows, one for each line of the order, all with the same order number depending on how many lines where on the order (which is generated via other software and cannot be altered). Therefore the data, when sorted by order number, appears in groups:

[URL] .....

Is it possible to conditional format each line in groups of the same order number to make it easier to differentiate between each individual order which will still work when the data is sorted or filtered?

View 2 Replies View Related

Take A List Of Data And Then Subtotal The Value By Certain Groups

Dec 4, 2009

I want to take a list of data and then subtotal the value by certain groups eg Toys. I then want to sort those subtotals by value descending. I then want to rank the sub totals

So basically i want to first select the group total and if that subtotal is the highest rank it. Its almost as if I need two subtotals (although Excel only gives me one)

View 4 Replies View Related

Selecting Groups Of Similar Data

Aug 5, 2009

I have ~1,000 rows of data utilizing columns A:C. The data is "grouped" by data that is in column C. Layout is as follows for example

Row01.....Column A......Column B.......Column C
Row02.....some data.....some data.....apples
Row03.....some data.....some data.....apples
Row04.....some data.....some data.....apples
Row05.....some data.....some data.....apples
Row06.....some data.....some data.....oranges
Row07.....some data.....some data.....oranges
Row08.....some data.....some data.....oranges
Row09.....some data.....some data.....oranges
Row10.....some data.....some data.....pears
Row11.....some data.....some data.....pears
Row12.....some data.....some data.....pears

I have been trying to come up with some code that will "select" groups of data and then perform an action on those cells. As an example:

View 5 Replies View Related

Extract Data Groups Into Their Own Workbooks

Feb 21, 2009

I have a worksheet filled with employee data. I.e. EMPLID, COUNTRY, MANAGER_ID, etc. I want to extract these employees into a new workbook (spreadsheet) by the employee's manager. So each workbook would contain every employee row data for only one manager.

View 9 Replies View Related

Copy Data Groups To Own Worksheets

Jan 13, 2008

I'd like to be able to have a macro that will create a pivot table based on a range, open up each of the total rows within the pivot table (which will create a new sheet), name the worksheet the title of the total row corresponding to it then copy each worksheet created by the pivot and paste into another sheet in a required format.

View 5 Replies View Related

Advanced Filter To Filter A List Of Data

Jan 14, 2010

How do I go about using an advanced filter to filter a list of data e.g.


and have the filter extract only the boat entries to another worksheet, so on another worksheet I end up with


View 9 Replies View Related

Advanced Filter :: Filter Data Between Two Dates

Mar 15, 2007

1- Force cell format date to by (yyyy/mm/dd) only, with worng msgbox( validation).

2- Make the first day of a month in a color cell

I've Tried this In Conditional Formating (=VALUE(right(A1;2))=1) but didn't work

3-Make Advanced Filter to filter data between two dates .

View 5 Replies View Related

Sort Groups Of Data And Maintain Rows?

Jan 3, 2014

I need to sort groups of data by a single cell in the group, compared to other single cells in other groups of data. The groups are always the same number of rows but vary in the number of rows. There is an example file below. The red is the company name and the green is what I need to sort the group by, as compared to the other groups. This example only has a few, the real file has thousands of groups. I want to sort the companies by the totals in green...they are currently in alphabetical order. There must be a way to do this without going the VBA option.

View 4 Replies View Related

Averages Of Different Groups Of Data In A Single Sheet

Jul 15, 2014

I have a fairly large set of data on one sheet containing numbers for 60 different people. The data is set up similarly to the set below:

John Smith 75
John Smith 80
John Smith 62

Jane Doe 49
Jane Doe 89
Jane Doe 66

So on and so forth until the 60th person. My question is: Is there a way to average the numbers for each different person at one time? What about median and mode as well? Do I need to set up a different sheet for that?

View 11 Replies View Related

Separate The Data From A Table Based On Certain Groups

May 12, 2009

I have a worksheet with 10000 records. For example, entire data in ColA-ColZ. In that, ColA-ColF - Personal Information; ColG-J Group1; ColK-N Group2; ColO-R Group3; ColS-U Group4; ColW-Z Group5. I this case, the data to be copied into another sheet as follows:

1) the personal data should be copied repeatedly.
2) Each Group data should be copied next to personal data.
3) The group's name is mentioned at the top of the datasheet.

I have attached a sample workbook for your kind reference.

View 2 Replies View Related

Highlight Duplicates Within Separate Groups Of Data

Oct 2, 2009

I have just looked through most of the postings I could find on the board regarding VBA to identify duplicates. Virtually all of these use the entire column when checking for duplicates.

I am after code which will identify duplicates in column A within a contiguous range of data.
For example there is a heading in column B and below this photo details will be entered and in the next column the photo number is entered.
Finally a number (for sorting) is entered in column A. The photos will be numbered 1 to whatever. So I need code which will highlight duplicates in that contiguous range of data.
Then there is one or two blank rows before another batch of photo details are entered. This means the same sorting numbers are used and are separated by a blank row or rows.

******** ******************** src="<CENTER><TABLE cellSpacing=0 cellPadding=0 align=center>Microsoft Excel - DUPLICATES - Many Macros.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA29=ABCD29 PN3215  301SOUTHERN APPROACH9107 313NORTHERN APPROACH9105 322GENERAL UNDERSIDE OF WESTERN STONE ARCH9111 331WESTERN ELEVATION9104 343GENERAL UNDERSIDE9110 35    36 PN7583  371SOUTHERN APPROACH9111 382NORTHERN APPROACH9112 391GENERAL UNDERSIDE9113 Sheet 9 [HtmlMaker 2.41] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

View 9 Replies View Related

Transpose Groups Of Data Where Each Group Is A Different Size

Jan 13, 2010

way to transpose groups of data where each group is a different size. To clarify, my data looks like this (all one column):

data 1
data 2
data 3

data 4
data 5

data 6

data 7
data 8
data 9
data 10

and I would like to get it to look like
data 1 data 2 data 3
data 4 data 5
data 6
data 7 data 8 data 9 data 10

That is, to transpose each group of rows before a space.

View 9 Replies View Related

Separate Data Groups To Seperate Worksheets

Oct 10, 2007

I have a data sheet of 190,000 lines. It is a telecommunications bill with has approx 15 columns.

I need to separate the spreed sheet into the respective "call types" and put this data onto separate tabs, at the moment I do it all manually with Excel 2007.

Is there a way of automating this separation of data

View 9 Replies View Related

Copyrights 2005-15, All rights reserved