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
ADVERTISEMENT
Feb 19, 2013
I have a spreadsheet with columns and columns of data. What I'd like to do is identify which rows have blank/no data in all specified columns (H, K, O, S, U, W, and Y).
To clarify, I need to identify each row where all of these columns are blank (as opposed to each row where at least 1 column is blank).
View 3 Replies
View Related
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
Nov 7, 2005
I need a formula or function that will sum the number of occurences a
specific text string is entered into cells in column B. I only want to count
the occurences when a number that is in column A (on the same row as the
text) is equal to a specific number. Sounds easy but it's very frustrating.
View 9 Replies
View Related
Aug 6, 2009
Example:................
The key point is that I have over 400 columns grouped in three, offset by one row down for each (this is due to a different formula I have working). Each column is 60 numbers long (not including empty cells above data). I want to copy all of these columns into a single set of three columns on another sheet (Sheet2). (These will be the fifth, sixth, and seventh columns, E, F, and G. If it would be simpler, I could simply copy the third of each set of columns on Sheet1 (C, F, I) to column G of Sheet2. Then there would be a total of 144 columns being copied (60 numbers per column).) All of this copying has to be values-only as the original columns consist of formulas referencing outside cells. Sheet2 should look like this:.............
View 7 Replies
View Related
Sep 13, 2013
I have data in Columns A to P
Column B contains specific groups.
I need to show the following:
For each group what is the count in column P that is
View 5 Replies
View Related
Mar 28, 2014
How would I go about finding the "Number of Shirts Ordered" values in the top right?
View 1 Replies
View Related
Jan 28, 2010
=(SUMIF($J:$J, "Player1",$K:$K)+SUMIF($L:$L,"Player1",$M:$M)+SUMIF($N:$N,"Player1",$O:$O))-SUM($I:$I)
This looks up if Player 1 was in the top 3 scoring zones and adds up the players total score.
I have another column in this table of data labelled 'Number of Players'. I was wondering how to lookup the total score for this player in the games he played under 6 players for example and the games he played in where there were other six players.
I've tried using that formula ^^ with variations of IF and SUMIF formulas to try and figure it out, but I keep getting formula errors or it just returns with the total score, and not just with the total score under 6 players.
I am also trying to create a graph of date on the x axis against score to date on the y axis - everytime I try, the x axis is always numbers instead of the date?
View 8 Replies
View Related
Jul 24, 2014
I want to count the number of groups of 3 adjacent cells in a horizontal range (C5:EV5) in which any cell in the group of 3 has a value in it (as opposed to all the cells being blank or containing "0"). For example, if any or all of the cells in C5:E5 have a value in it, it would count as 1, and if any of the cells in the group F5:H5 have a value, it would also count as 1, and so on. Is there a way to use COUNTIF for this?
View 5 Replies
View Related
Sep 9, 2009
I have few macros that I use to reconfigure a particular workbook into a sort of "developer mode". I keep them in a text file that I can paste them from when I'm working on it and then delete them before I send it out to other people.
I'd like to be able to script them so they only run if the workbook is open on a specific machine (namely mine). Is there any way to identify the specific computer a spreadsheet is being run on?
View 2 Replies
View Related
Feb 14, 2013
I have a complex report with thousands of lines I am trying to clean up. In order to do this, I need VBA that recognizes specific text data starting after row 9. When it sees this, it will need to select and delete the 2 rows above it, the row it is in, and the 6 rows below it. 9 rows in total, some of which are blank. The text will repeat every 47-50 rows, but I cannot say exactly when it will repeat again. When it is repeated, I need the 9 rows deleted. At this time, there is only 1 column in the report because I need to split out the data in the cell into adjoining cells. Since I have not solved that yet, I need to just delete the entire row.
The text is written with spaces between each letter and a double space between words. It reads, "B I L L I N G M A S T E R I N V O I C E S E T U P L I S T I N G"
View 8 Replies
View Related
Aug 27, 2006
I am developing a macro to select cells within a worksheet based on certain criteria and to make a list of their range names. My approach is to copy the range names to a column of cells using something like the following text:
Sheets("mySheet").Range("myRange").Name.Copy
but I get an error message saying "Object doesn't support this property or method". Why can't I copy a range name, and is there a way to work around this?
View 9 Replies
View Related
Jan 21, 2014
I am trying to identify repeat customers with a view to removing the purchase if the customer buys the same product within 5 days.
If the same customer buys a different product each day then I would like to count each purchase.
If the same customer buys the same product every day I would only like to count it once every 5 days.
I have tried using COUNTIFS for this. Formula in column D where A contains the Customer, B the Purchase Date and C the Product:
Formula:
View 5 Replies
View Related
Nov 22, 2008
I must put in descendent order some columns, but the thing is a bit more complicated...
As my english is not very good i am gonna explain it with a simple example:
----------------------------------
1 4 2 5 rabit fox dog cat
2 6 1 3 bird fis wolf mouse
----------------------------------------
Ok, i must order the numbers in a ascendent way, but reordering one number means also reordering its correspondient name (the name of the animals at its right).
So, again, with an example you will see it better:
Ordering the first row it must appear like this:
----------------------------------------------
1 2 4 5 rabit dog fox cat
----------------------------------------------
I didnt know if i had to post it here or in other subforum and either if it can be done with excel.
View 11 Replies
View Related
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
Jan 28, 2010
Is there a macro which would group columns automatically
View 2 Replies
View Related
Nov 3, 2009
So i'm try to automate a process that I currently do using filters.
Here is the problem:
I have a list of text in a column (usually 700 or so entries).
I need to remove any entries that contain 1 of 42 keywords.
So I need a fast way to search each cell for each one of the 42 key words and flag the cell for deletion if contains anyone of the 42.
example
A
1 This field has keyword1
2 This field has keyword2
3 This field doesn't have any of the 42 keywords
somewhere else I would have a list of keywords in a column.
View 9 Replies
View Related
Mar 17, 2013
I've got a large spreadsheet with many cells that need user input. A bunch of calculations are performed on subsequent worksheets, but I want the user to only see the fields they need to enter. I'm trying to write some code that will use a scrollbar (form control, not activex) to only show one group of columns at a time. For example, if ScrollBar1.Value = 1, then show columns "A:D" and hide colums "E:Z". If ScrollBar1.Value = 2, show colums "E:H" but hide columns "A:D" and "I:Z", etc.
Here's the code I'm trying to get going, but I keep getting various errors when I try to execute.
Code:
Private Sub ScrollBar1_Change()
Dim v As Integer
v = ScrollBar1.Value
[Code].....
As it stands now, when I click the scrollbar I get the error: "Compile error. Method or data member not found," and the ".Value" in Line 3 is highlighted.
View 4 Replies
View Related
Jan 18, 2014
I'm looking for a way to count groups of alternating rows of "TRUE" values across 3 columns. There will never be an occurrence of more than one "TRUE" value per row. In the sample below, alternating TRUE values occur in rows 4 through 7, so this would be counted with the groups of 4.
1Column 1Column 2Column 3
2FALSEFALSETRUE
3FALSEFALSETRUE
4FALSE TRUEFALSE
5 TRUEFALSEFALSE
6FALSE TRUEFALSE
7 TRUEFALSEFALSE
8FALSEFALSETRUE
9FALSEFALSETRUE
10FALSEFALSETRUE
View 4 Replies
View Related
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
Jun 11, 2014
I have a workbook with 25+ worksheets, all identical formats that each represent a different client. Each client is assigned a project team. I want to be able to sum up the worksheets by project team, but have flexiblity in doing so when I add new clients etc. So a couple of thoughts.
1. Is there a formula that I can create that compares the worksheet name to a list of names assigned to a particular client?
2. Can I reference a cell in each worksheet that identifies that sheet as a client that is to be included in the team summary?
Example of worksheet sequence
Worksheet - Summary Sheet - All Clients
Worksheet - Client 1 Team Alpha
Worksheet - Client 2 Team Beta
Worksheet - Client 3 Team Alpha
Worksheet - Client 4 Team Beta
Worksheet - Client 5 Team Alpha
Worksheet - Client 6 Team Beta
I want to create summary worksheets (identical to the individual client sheets) for each team.
View 1 Replies
View Related
Mar 9, 2009
How to convert multiple Rows recors to a single row record in a Notes(csv) format? Have update my xls file. My source is in the below format(Source.xls):
GroupName_A,Name_A
GroupName_A,Name_B
GroupName_A,Name_C
GroupName_B,Name_D
GroupName_B,Name_E
GroupName_B,Name_F
GroupName_B,Name_G
GroupName_B,Name_H
GroupName_B,Name_I
I want to convert it to a CSV file where by it can be import to Lotus Notes (output.xls):
1,1,Group,GroupName_A,"Name_A,Name_B,Name_C","CN=John Sam/OU=FIN/OU=staff/O=IBM,CN=Mary Flow/OU=FIN/OU=staff/O=IBM",CN=John Sam/OU=FIN/OU=staff/O=IBM
1,1,Group,GroupName_B,"Name_D,Name_E,Name_F,Name_G,Name_H,Name_I","CN=John Sam/OU=FIN/OU=staff/O=IBM,CN=Mary Flow/OU=FIN/OU=staff/O=IBM",CN=John Sam/OU=FIN/OU=staff/O=IBM
As you can see only GroupNameN, and Name_N are varibles, the rest of the fields are static. note that there is opening and closing quota for column "E" and "F" in output.xls
View 5 Replies
View Related
Mar 30, 2007
I realize that the easier resolve to this request is to use Access & we are moving to that application.
I have included a worksheet as an example.
We have a 'work in progress' (WIP) worksheet that contains 12 rows of data per job. Need to know how (or if) you can sort by the "Job #:" value while maintaining the group of information necessary for each individual job.
Not a normal sort because the values aren't entered in typical side-by-side row & column format.
View 10 Replies
View Related
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
View Related
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)
Highlight:
Ana M. 12 A St 333-3333
Ana M. 23 Z St 333-3333
Skip:
Jane S. 12 A St 555-5555
Jane S. 15 Z St 555-5555
Highligt:
Sam A. 55 A St 222-2222
Skip:
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
Apr 14, 2009
It's a time sheet that I need to have whatever data is listed in column E to be listed starting in cell C27 but only once even if it's listed several times. In cell D27 I need the sums to match the data to it's left with the values from above in column D. I've used fill colors to help show my intent. I would also like it to keep track of remaining vacation time by recognizing the word 'VAC' from column E and subtract the value from it's left. This value is shown in cell J26 as '46hrs REMAINING'.
View 4 Replies
View Related
Jun 1, 2012
I have two columns of B2 and C2 data in the same worksheet and want to identify duplicates in the columns and return a value of true or false in column D. The formula I have been utilizing is:
=IF(ISNA(VLOOKUP(B2,$C$2:$C$5000,2,FALSE)),0,VLOOKUP(B2,$C$2:$C$5000,2,FALSE))
However, i receive a #REF# return in D. What am I missing ?
View 6 Replies
View Related
Apr 9, 2014
I have a macro-routine that generates a table of all sheet-headers (+/- 100-120 sheets) in a workbook. It's crusial that all headers - row("1:1") - have the same values in respektiv columns -so I have this routine to check that this really is the case, before new prosess. The table is rather huge - so I should som way or another identify the columns with more than one value (f.ex back-ground-color in column-top-cell). In this case I must edit and correct the header, and run my routine once again - until all headers are the same
Table is plased in Sheetc("HeaderCheck")
My routine works, but I dont know how to fix this mark-up stuff.
View 3 Replies
View Related
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
May 18, 2007
The following code takes the selected item from a listbox and populates another listbox with the item. The problem is that only the item from column 1 is populated to the other listbox. How can I populate the other listbox with the items from both column 1 and column 2?
Private Sub cmdSelToExc_Click()
Dim i As Long
With Me.lstProducts
' Copy all selected items, then delete.
For i = 0 To .ListCount - 1
If .Selected(i) Then
Me.lstExclude.AddItem .List(i)
End If
Next j
Next i
For i = .ListCount - 1 To 0 Step -1
If .Selected(i) Then
.RemoveItem i
End If
Next i
End With
End Sub
View 2 Replies
View Related