Groups In A Column

Dec 13, 2008

I have work sheet, ("Sheet1"), there are about 17000 plus entries in two columns A and B,. In Column C, following formula is inserted to show the "number of occurrences" of a Group in Column A.

=IF(ISBLANK(F4),"", IF(COUNTIF($F$2:$F$118,F4)=1, "", COUNTIF($F$2:$F$118,F4) & " occurances"))

Mine active worksheet is Sheet2. In Column A, an entry is made and Column B fetches the equivalent entry from Column Sheet1:B. The formula is :

=IF(ISNUMBER(MATCH(A1, Sheet1!$A:$A,0)), INDEX(Sheet1!$A:$C, MATCH(A1, Sheet1!$A:$A, 0), 2),
IF(ISNUMBER(MATCH(A1, Sheet1!$B:$B, 0)), INDEX(Sheet1!$A:$C, MATCH(A1, Sheet1!$B:$B, 0), 1),""))

When an entry will be made in Column A of Sheet 2, naturally it brings the equal of Column B. What I will like that in the subsequent columns, C,D,E,F,G,H, etc. the values may be reflected for all the occurences equal to entered in A1. For example if in Sheet 1, :

1234 xyzxyz
1234 bxyxdx
1234 ffffffff
1234 ggggg

So these are four occurences. in Sheet2, when one entry of 1234 will be made it will show any of the four values, the rest three values may be reflected in subsequent columns.

View 7 Replies


Transpose Multi-Column Groups/Repeats Based On 1 Column

Mar 19, 2009

I have a sheet with 3,000 rows and only two columns. Column "A" consists of 20 to 30 different names, column "B" consists of 50 to 60 different products. I need to be able to evalute the value in column A and copy, paste special transpose all values in Column B that have the same value in column A. As an example if cells A1 through A5 is "Arizona" and cell B1 is Broccoli, B2 is Cauliflower, B3 is Apples, B4 is Oranges, and B5 is Bananas, I want to copy B1 through B5 and paste special transpose to cell C1.

This then would need to loop all the way to the bottom of the data in Column A looking for a change in value. The attached file called Sample Data has two tabs. The one titled "report" shows the raw data, the one titled "Final" shows how I would like the results to appear (column L)

View 6 Replies View Related

Formula To SUM Different Groups In Same Column?

Aug 22, 2014

Attached workbook is self explanatory but Cols B-C have matching data in groups with a value for each row in Col D.

I need a formula to SUM each group and input that SUM value in the adjacent Col/Row E

View 6 Replies View Related

Comparing And Sorting Multiple Column Groups?

Feb 28, 2014

I'm am trying to take multiple column-groups and sort them by product ID number.

I am trying to put together a sales record for the past 26 months for around 2000 different items and sorting them manually would obviously be a nightmare.

I would like to sort them so that all the drugs with the same NDC (prod ID) have the correct data for each month displayed on the correct row for that NDC. Right now I have each set of data for that month sorted by product ID number but some months we used certain products and others we did not. Is there any sort of Macro that I could use? What I am trying to do I attached two files. The "before" and my desired "after".

starting point.xlsx
end product.xlsx

View 1 Replies View Related

Permutations/Combinations For Column Relative To Groups In Another

Aug 23, 2009

I need to figure out how I can get excel to write me a list of permutations / combinations for the Group (column a) and Item (column b) columns in the example. The group designates the items that should be inlcuded in the combination / permutation in Related Items (column c). I have thousands of lines to do this with so I need to figure a way to automate it. I have started the Related Items column which is the results I need output. If it could format it like this adding in the space between the rows as it outputs the answer that would be perfect. Anyone know how to do this? I've found many answers, but none that have 2 columns with separate grouping within the column.

View 4 Replies View Related

Transpose Inconsistent Data Row Groups In Column

Jul 1, 2008

I have a data set in an Excel spreadsheet entered as one column of data. It is names with addresses, phone numbers, job title, etc. I want to select and transpose each person in the list so when I am done the person's name is in column A, Company name in column B and so on. The problem is the information listed is not the same for each person - so there is a different number of rows for each person. The names are in bold text though, so I need to select from one cell with bold text to the row BEFORE the next row with bold text and transpose the data for each selection. I found this forum by finding an old question here that is similar at Copy based on Bold Paste-transpose provided in that post and it produced no result.

View 4 Replies View Related

Create Worksheets Based On Data Groups In Column

Apr 10, 2008

I have a workbook that lists system analysts and information on the systems they're responsible for. I would like to have an Excel macro that will:

1. Create and name a new worksheet for each UNIQUE value in the 'Name' column (new worksheet for each analyst)
2. Copy their system info to each respective worksheet

I've attached a sample workbook.

View 2 Replies View Related

Calculations On First Lines For Entire Groups Of Data In Single Column?

Feb 25, 2014

I'm looking to do some simple maths with ranges in Excel but I'd like a way to be able to input all these formulas automatically although I am pretty sure a single formula per column could do it all automatically. I've got to put the sums in AD to AF on the first line of each location, but the maths is for the entire location's range.


View 3 Replies View Related

Fill Range With Sequential Numbers Based On Corresponding Column Groups

Feb 15, 2010

In the attached workbook I'm trying to populate Column E with sequential numbers (as shown) based upon a changing range (defined as a named range called 'range'). Is it possible to write a formula in the cells in Column E that will do this?

View 2 Replies View Related

Split & Copy Groups/Segments Based On Cell Value In Column

May 6, 2008

This is my first post but I have been using Ozgrid for awhile now. I am farily good with excel formulas but have just started with macros so bear with me if i dont understand what you mean at first.

I am looking for a way to copy rows our of sheet2 in the attached sheet based on the value in the segments column in sheet 2. The rows need to be paste into sheet3 (already has heading set up). The segments value is the number of times i need each row copied into the next sheet. The purpose of this is to split random length samples into 10cm incriments for study. For example, a 1.5m sample is taken so there should be 15 segments of 10cm each copied into sheet3.

Also, if possible, it would be nice for it to display the actual length of the segment after copied into sheet3 for cases where the length was not evenly divisible by 10. I have found several examples of row copying macros, but none that will copy a conditional number of hte same row based on a cell value. In the original data there are close to 4000 rows, but the number of rows will vary depending on the data source.

Another thought I had was if the total number of available rows is going to be exceded would it be possible to have the rows pasted into different sheets based on the rock type listed in the column?

View 9 Replies View Related

Create A Function That Numbers Rows With Respect To Data Groups In A Column

Dec 1, 2007

I need to create a function that numbers rows with respect to data groups in a column (column labeled "Type" in this example). The result would be that shown in column A in this table.

How do I write the function?
#TypeName Date


View 10 Replies View Related

Macro To Automatically Sort Multiple Column Groups Separately As Data Is Entered

May 20, 2014

I have a worksheet that contains multiple task lists, each having two columns, a "Priority" field and a "Description" field. The data should be sorted by Priority first and by Description second. The header row is 5 and the data is in rows 6 through 50. The first list is in A5:B50, the second C5:D50, and so on until the sixth list in K5:L50. I have a macro that works for one task list, but cannot get it to function for multiple task lists. Below is the macro that functions for the first task list. It is in the code for the sheet tab.

Option Explicit
Private Sub Worksheet_Change(ByVal Target1 As Range)
If Target1.Column = 1 Then


View 1 Replies View Related

Excel 2010 :: Combining 100% Stacked Column With 100% Stacked Line Using 2 Groups Of Data?

Apr 24, 2014

I am trying to create a 100% stacked column which has a superimposed line chart over the top of it.

Basically % of yes and no for two separate datasets, but which share the same characteristics. The x-axis is months of the year.

All Yes
All No



I can create 2 separate charts. One for the yes/no of one set as a 100% stacked column. I then use 'no fill' on the No column to stop this from being seen.

I can create a 100% line chart for the second data type, and 'no fill' the 100% line that appears.

However, I can't merge the two charts to show the Yes % for one dataset as bars and the Yes % of the other as a line.

I am using Excel 2010 on XP.

View 2 Replies View Related

Count Number Of Clusters / Groups Of Given Number Occurs In Column

Mar 2, 2012

I have data in a column (G) consisting of zero and ones. I would like to count the number of clusters of the number 1 in the data. For example in the data below there are 8 ones. But instead, I need to calculate how many groups of 1s occur. So in the case below the group of 1s = 3.

In terms of what defines a group. Whenever there is a zero either before or after the occurance of a 1 constitues a group, i.e the groups are broken out by zeros.


View 3 Replies View Related

Sum Of Groups ..

Nov 13, 2008

I have a sheet of over 60000 rows a small section of which I attach..

I need a formula in cell d2 to give the total for c2 to c10,d3 to d10 will contain this same total...cell a11 starts the next group and the formula for the group total will be in d11 to d18 etc

View 9 Replies View Related

Formula - Age Groups

Mar 19, 2007

i am setting up a work sheet on ages for a sporting team

i want to be able to type in the date of birth and have it automatically come up with there age then for it to go to an age group


14-3-00 would go to 7 yrs then go to under 8's

View 11 Replies View Related

How To Sum Groups Of Cells

Jan 23, 2013

I am trying to sum groups of cells where the groups contain a variable number of cells (within a column). My sheet has the following columns, C = plot#, G = plant#(within the plot), and F = number of tillers(for each plant). I need to get the total number of tillers per plot. Plots have different number of plants, so for instance cells c11:c16 are plot 3, cells g11:g16 are number 1 thru 6 (plant#), so I need the total number of tillers which would be sum(f11:f16). But each plot has a different number of plants. I've been told I'm not the best at explaining things,

View 2 Replies View Related

Comparing Value To Three Groups

Mar 2, 2007

Here's one:

Column C (starting at C6) contains the team number. I want column B to find out if the team number is an AM, Mid or PM team.

I have listed the AM teams at J1:U1, the Mid teams at J2:K2 and the PM teams at J3:V3.

How can the formula in column B search those ranges and determine if the team in column C is AM, Mid or PM?

View 9 Replies View Related

VBA For Repeating Groups

Apr 3, 2007

VBA code. My sales data is arranged as months in columns and customers in rows. I have about 36 months of data that must be formatted as a single date column for pivot table manipulation.

View 9 Replies View Related

Summing Groups

Apr 16, 2008

What I want is to sum any/all groups of numbers larger than 0 that are grouped in 4 or more....e.g. in my example D1:G1. The answer should be 150.... I'm trying this formula: ...

View 9 Replies View Related

All Possible Combinations From Groups

Nov 11, 2009

I have a set of 13 groups, each with anywhere from 1-9 different variables for a total of 56 variables. I want to find all the possible combinations of 1-5 variables. Only one variable can be used from each group in a combination. Please see attached data sheet.

View 5 Replies View Related

Sort By Groups

Jan 14, 2010

I've been trying to rank by groups within one list of data; I can sort by two columns and rank with a simple "if" statement, but this is no longer practical for how often the data is updated and re-sorted. Trying to find a dynamic formula that creates ranks by the groups in a selected column. Example attached.

View 5 Replies View Related

How To Align Groups Of Columns

Jun 3, 2014

I would like to align a group of columns against another group of columns.

Columns G-L will not be changing. They have a certain amount of info I want to align with columns A-F

There are 30000 items in G-L and only around 3000 so far in A-F

Weekly I will import and reorder A-F and then want to realign them with G-L

This is a link to a sample of the original file: [URL] .....

This a link to how I wan the order to be : [URL] .....

As you can see there are many more rows in columns G-L

I will update columns A-F weekly and then want to realign G-L

View 2 Replies View Related

Formula To Find Age Groups

Nov 8, 2008

I have a column with customers ages in it. Now what I'd like to do is create a formula that checks all the age cells, then gives a percentage for each age group of my choosing. I tried some IF formulas but I just can't figure it out

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

Counting Groups In A List

Dec 12, 2012

I have attached a file to better describe my issue, essentially I would like to count all the 'A's, all the 'B's and so on in the file and have the count next to it, so something like

A 6
B 3
D 5
F 1

In the file I uploaded I have column B as a count of the numbers, is there a way I could get just the final number for each letter, that would solve my problem.


View 4 Replies View Related

Permutations Of A List (groups Of 3)

Aug 31, 2007

Is there any way to determine all possible unique combinations (or sets) of any 3 strings in a table? I need to make a master set list from a list of strings which could be over 100 elements long.

Once I have a result set, I need to get rid of any groups which have a value contained in another group.

cells A1 through B100 contain:
Item1 45
Item2 50
Item3 98
Item100 75

Result set would contain groups of any 3 item combinations where the corresponding "B" value is within a certain range (between 40 and 80), while also trying to create sets with the lowest possible sum (placed in the 4th column) of the 3 numbers.

One possible combination would be:
Item1 Item2 Item100 170


I need to list all possible combinations, without re-using an element from a previous (lower sum) combination that matched the criteria.

View 13 Replies View Related

Groups Columns Automatically

Jan 28, 2010

Is there a macro which would group columns automatically

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

Subtotals In Groups Vs Filters

Dec 12, 2008

Subtotal doesn't add cells hidden under a filter column but it does when grouping. How can I get groups to change a subtotal based on whether they are hidden or not. What I'm really trying to do is use conditional formatting to change the format when a group is expanded vs collapsed.

View 3 Replies View Related

Copyrights 2005-15, All rights reserved