Checking For Unique Value In Matrix And Group Column Value Assigned
May 16, 2014
I'm working on a sheet I have to complete and I'm blocked 'cause I'm not able to find the right formula to get the result I need.
I have a sheet that contains 4 columns with the following content (consider the following just as an example. The real sheet contains more that 25,000 rows.
User ID Repository 1 Repository 2 Repository 3
001FG x
001FG x
10GA x
20PK x
20PK x
20PK x
21CC x
4C1D x
Now, the table contains the user id (unique ID) and three columns that stand for the access right the user has for accessing a specific repository.
This means that a User can have more than one occurrence in the sheet because it could have rights to access different repository (i.e. the user 20PK can access all the repository).
What I should be able to do is to transform the table above to a new one with unique user id and the rights for each repository. I would need something like this:
User ID Repository 1 Repository 2 Repository 3
001FG x x
10GA x
20PK x x x
21CC x
4C1D x
How can I do this by using a formula and not a macro?
View 3 Replies
ADVERTISEMENT
Feb 20, 2014
So far I have this just for testing but I do not know what I would do to have the user select a region and for me to test if it is a valid size for a matrix. (2x2 or 3x3 are the valid sizes).
Code:
Public Function MyInverse() As Variant
Sub Size()
Dim row As Double
Dim col As Double
[Code] ...........
Else:
MsgBox "You have entered an invalid size of the matrix. Please enter a 2x2 or 3x3 matrix."
End If
End Sub
View 2 Replies
View Related
Jun 1, 2006
I am trying to filter a list where the number of occurences of unique cost codes for each emp No is more than 1. What is needed is the emp no's who have only one costcode to have '0' in the 'no of codes' column. If more costcodes per empno, I need the first costcode to have 1, second code 2, etc in 'no of codes' column. The idea is at the end to be able to filter and delete all those with '0' in, as I only need to work with emp no's that have more than 1 'cost code'. See attached file. The records are in col A, the costcodes assigned to them in B, the formula in C, at the moment is:
=IF(AND( COUNTIF($A$1:A2,A2)>1, COUNTIF(B1:B2,B2)>1),C1, IF(AND(COUNTIF($A$1:A2,A2)>1, COUNTIF(B1:B2,B2)<2),(C1+1),1))
View 4 Replies
View Related
May 27, 2014
There are groups of similar ID numbers in Column J. For a group of similar ID numbers in consecutive rows there is only one row that has a number greater than 0 in its Column L cell and the rest of the cells of Column L for that set of similar IDs is filled with 0s.
First for that unique ID group I need to find out which row is it that has a value greater than zero in its Column L cell.
Then I need to use that value to fill the rest of the 0s in Column L corresponding to that set of Unique IDs.
The process continues with identifying similar IDs in Column J and this time doing the same thing for their Column M. I have attached a sample file that shows the data and how the results need to look like.
View 3 Replies
View Related
Dec 20, 2011
I have folders with different assigned folder icons based on the content in the folders.
I would like to arrange these folders in the window based on the assigned folder icons and then by alphabetical order.
How it is to be done. Can we make use of vba having the folders path list in one column of excel sheet?
View 9 Replies
View Related
Jul 18, 2014
Unique Count situation based on multiple criteria
Scenario: we have agents from different departments who produce different sales on different plan rates
Raw Data
Employee
Plan Rate
Payout pts
[Code]....
View 9 Replies
View Related
Mar 21, 2014
For a table like the one below produced for the sake of example (actual is much much bigger) I want to make it list rows that are true for a certain column for a certain variable in the matrix. So for say water terrain, which types of activity can I do i.e. swimming. Or for Offroad the activites which I can't do i.e. Run and Swim.
ActivityWaterRoadOffroad
Jog nym
Run nyn
Walk nyy
Swim ynn
y=yes
n=no
m=maybe
View 10 Replies
View Related
Jun 10, 2014
I have a little trouble with my macros. I wrote several different macros, each for a special purpose. Then I created some Buttons, which would make it easy for others to work with them.
But I have now experienced several times, that my buttons don't keep their assigned macro.
View 5 Replies
View Related
Mar 19, 2014
How would I fix my formulas from not working when I filter other columns? It is a simple =A1/A3 cell and this calculation is down the full column. When I filter any column, the calculation does not stay with the assigned cells.
View 2 Replies
View Related
Apr 16, 2014
Using Excel 2013.
Is it possible to get a unique count in a group subtotal though the elements in the group may not be unique using Pivot Tables w/o resorting to Power Pivot?
Ex
Group 123
Group 456
Are both members of NorthDivision
If someone is in Group 123 for 9 months, then Group 456 for 3 months, that membership should be reflected in the correct group accordingly.However, that person should only be counted once in the NorthDivision, not twice.
Output:
Group 123 0.75
Group 456 0.25
NorthDivision 1
At first glance, it appears easy, however, what if membership is only total 3 months?Then Group would increment 0.25, but I need NorthDivision to increment 1
View 5 Replies
View Related
Aug 19, 2014
I have 5 different groups for that i have to make XY scatter graphs and my data looks like this
XY
125
13
156
145
134
234
248
260
2102
2105
3125
3148
3198
3298
3245
4386
4369
4234
4465
4898
5902
5907
5345
5234
5209
now i want to make 5 different graphs one per each group. Now i am making them manually in excel but it is taking lot of time to format each graphs to get unique graphs. I am attching sample file and graph for better understanding.
View 3 Replies
View Related
Jun 26, 2013
I use the following formula to count unique values within a group for a pivot:
=IF(SUMPRODUCT(($CO$2:$CO55681=CO55681)*($E$2:$E55681=E55681))>1,0,1)
When I paste values over the formula it takes around 1 hours to do the 70k cells which is excessive. I want to know if there is a way to speed it up?
I tried doing an =cell and then paste special that and it works really fast but when I delete the actual formula's again the whole sheet locks up.
View 9 Replies
View Related
May 23, 2009
I've searched the forum and believe this thread to be the closest to what im looking for, but its doesnt completely apply: matrix to list conversion. I have an attachment to support my questions (see attached). I have a matrix of words (strings) that contain repeating and non-repeating contents. The matrix also has spaces which are of no value. Essentially, the VBA needs to ' analyze' this matrix and create a consolidated frequency list (as shown in the file).
The matrices are HUGE and therefore some of the clumsy VBA i am using is turning out to be a bit inefficient. The file also contains formulas and such that I cannot use AutoFilter nor can I use Insert or Delete rows...so the VBA shouldnt use those either.
View 6 Replies
View Related
Apr 15, 2009
I am a new member, with not a lot of VBA experience. Sorry for the vague title .. couldn't come up with something ^^". The idea is, I made an list with several anime titles I am watching (in column A) In column G it shows the status; "Watching", "Completed", "Planning to Watch", "On-Hold" or "Dropped". I managed to let the colors of those words change with this
View 4 Replies
View Related
Jan 29, 2009
I NEED some code to convert a matrix to a singel column and it should be reversed to!
see attachment!
example:
FROM:
A1 B1 C1 D1
A2 B2 C2 D2
A3 B3 C3 D3
TO:
D1
C1
B1
A1
D2
C2
B2
A2
View 4 Replies
View Related
Nov 23, 2009
I have this code to take an area of data and perform the SUMXMY2 formula and output the results to another sheet. My problem is that the results are being outputted in a semi-matrix and i needed them in a column so i can them perform a sort. Is this possible and can anyone shed a light on the best way to do it?
View 2 Replies
View Related
Dec 11, 2011
I'm trying to create a questionnaire and in each question you got to rank the answers 1, 2, 3 and 4 according to your preferences.
So I'm trying to use data validation to allow ranges between 1 and 4 and at the same time I'm trying to use =countif($A$1:$A$20,A1)=1 to prevent repeating selections but my problem is that with the data validation tool I can only select one of those options either allowing whole numbers between 1 - 4 or allowing custom and using =countif($A$1:$A$20,A1)=1.
Is there a way to do both?
View 6 Replies
View Related
Jan 2, 2006
I have an array of data with column headings and changeable row "item names"
in the left most column. The intersecting matrix of data contains quantity
values at the intersection cells of column and row headings. I need to
automatically reorganize the data as a list in a column with the row "Item
Names" at the left but and repeated as necessary but instead of the
additional columns of quantity values, I need to have this data listed in a
single vertical column along with an additional column with the original
column header that corresponds to the quantity value. I think there must be
some kind of lookup function to do this but I can't find the right way to do
it.
View 13 Replies
View Related
Aug 26, 2007
I have a data chart on 200 people with overlapping membership in 20+ groups, represeted as binary (1=member, 0=notmember), for example: ...
View 9 Replies
View Related
Aug 22, 2008
Using VBA, I wish to work out the inverse matrix of a large matrix (100*100), but keep getting the # Num! Error. I am using the minverse function. I have defined variable as "variant", does this give me the same possiblities in terms of number size as the variable "Double"?
View 9 Replies
View Related
Aug 11, 2014
I have a requirements in excel vba to evaluate and check the column data from other workbook. I would like to filter remarks for all "INTRANSIT" from my working file then If the data or tracking# is exist from the source file evaluate if transaction remarks is "Done" or if "Not yet" exist. If remarks from SourceFile is "Done" i will put "RECEIVED" or If "Not yet" i will put "INTRANSIT" in my Working file Remarks column.
Workbook WorkingFile:
Tracking# QtyModelRemarks
---------------------------------------------------
D-8840862402|20llllRECEIVED
D-8840862402|20kkkkRECEIVED
D-8840862411|20mmmINTRANSIT
D-8840865000|230mmmINTRANSIT
D-8840865000|50bbbbINTRANSIT
D-8840862450|20bbbbINTRANSIT
D-8840862450|120bbbbINTRANSIT
SouceFile --- to evaluate if Done or Not Yet
Tracking# QtyModelRemarks
----------------------------------------------------------
D-8840862402|20llllDone
D-8840862402|20kkkkDone
D-8840862411|20mmmNot Yet
D-8840865000|230mmmNot Yet
D-8840865000|50bbbbNot Yet
D-8840862450|20bbbbNot Yet
D-8840862450|120bbbbNot Yet
View 11 Replies
View Related
Sep 2, 2009
I have a workbook with 2 sheets. What I want is:
1. Cells in column A, Sheet 2 are compared with the Cells in column A, Sheet 1.
2. Sheet 3 is produced containing all the rows in Sheet 2 for which there is no duplicate cell in Column A, Sheet 1.
3. The rows copied from Sheet 2 to Sheet 3 are highlighted on Sheet 2. Attached is a workbook with 3 sheets. Sheet 3 is the desired result (+ the cell highlighting on non-duplicates on Sheet 2).
View 5 Replies
View Related
May 27, 2014
I have a matrix that looks like this:
1
2
3
4
5
6
7
8
9
10
11
12
And would like to rearrange this data to:
1
2
3
4
5
6
7
8
9
10
11
12
In actuality it's a 110000 x 3 matrix that should become a 330000 x 1 column.
View 6 Replies
View Related
May 5, 2008
I need a macro that allows me to select a matrix of cells and converts it into a single column elsewhere, pasting the second column below the first and so on. Is it possible to select the matrix, hit a macro key and then paste it in my selected location?
View 11 Replies
View Related
Mar 5, 2008
I have a 10x10 array that represents different cities that a travelling saleperson can travel to. Rows are cities designated as i values, columns are the same cities and represented by j values. I need to use a For, Next loop to determine the shortest distance (lowest value) in a given column. The i (row) that contained the lowest value is the first city to be visted and a boolean is entered for that j=i column, showing that the city has been visited. When pulling the minimum values from the column I need to ignore 0 values where the distance is between a city and itself. I'm having trouble coming up with a loop that takes identifies the i row with the lowest value that also ignores previously visited cities and takes the boolean into account. Maybe my Excel spreadhseet will clear up what I'm trying to do, The distances were generated using RANDBETWEEN(1,100).
View 8 Replies
View Related
Oct 2, 2008
I'm trying to figure out if there is a formula I could use that will calculate the average of a group cells in one column based on the condition of another column. It's hard to explain, so I will show an example. All the data is on a one worksheet and I'm trying to show totals and averages on another worksheet. Location, Days
17, 4
17, 3
17, 5
26, 4
26, 8
26, 10
26, 7
On a different worksheet I would want to know what the average days are for each location. So is there a formula that I could use that will look at column A for a specified location number and then average all the days in column B for that location? I'm using Excel 2003 and have tried using the Average(if) but with no success.
View 2 Replies
View Related
Nov 27, 2009
I've a worksheet that is 3750 rows of cells from A to DT and contains only numbers with the exception of a header row. The rows are in groups, primarily 4 rows, although there are exceptions - these rows are separated by blank rows. Small example as follows.
0:001:002:003:004:005:006:007:008:009:000000-296567-6-1-400000286567-300-230000715557-16-11-40-18141755226-348405-362203081417192125-893581061800-2443-11124-289326-81318-1105027854331331211318-10216-161235526785691450451115951-17-1882505656683093102671056-191-33-2710000554740971-61-78-34224107-169-128-17090179-85220-55-63-1-162-140-242-235328266319106113-214-64-148205-4108120-142076-167-60-68-6418081102-800000000000-3099695807185-80356138-123194186210677727125-23233092-2942944748055564476-422051-224-34461486649597117-84209-22-251-94
I have been trying to conditionally format each group of rows and each column and color the cell in each column of four (or less) numbers in each group that represents the maximum number.
It was easy enough to come up with the conditional formatting BUT it will take forever to do this manually. I've been told to use a macro but I know nothing of programming. Has anyone seen or heard of a macro that might already be written to perform this onerous task.
View 9 Replies
View Related
Dec 2, 2008
what i want to do is 1 have a workbook with 3 worksheets. Sheet1 contains the columns:
Reference No. Line Item No. Amount Date
1 3 500 12/01
2 4 900 01/01
Sheet2:
Reference No. Line Item No. Quantity PersonInCharge
1 3 10 Kim
5 6 60 Noel
For sheet 3, what i want to happen is that when i run the VBA macro, it searches through both worksheets (Sheet1 and Sheet2), finding rows that have matching Reference No. AND Line Item No. and combine all of the columns (and values) for that particular row into another row in Sheet 3. So for the above data set, the resulting data in Sheet 3 would be:
Reference No. Line Item No. Amount Date Quanity PersonInCharge
1 3 500 12/01 10 Kim
As this is the only entry in both sheet1 and sheet2 where there is a matching reference and line item no.
View 9 Replies
View Related
Apr 25, 2013
I am trying to analyse a set of scores of different doubles tennis tournaments - new tournaments and new players are continuously being added and so need some dynamic way to analyse. I would like to extract sort of a ranking system based on the scores by each player and one way to do so is to analyse the scores by tournament. Basically the same players play in different tournaments and i want to be able to know who performs better than others i.e. get better total scores during a tournament.
The attached gives an example of the data i have as well as the result i would like to achieve. It does not really matter if a formula system or a pivot solution is provided as long as i achieve the result, and can add to it as new data is populated. I have been trying all sort of solutions using pivots, matches, lookups etc but no luck yet.
View 7 Replies
View Related
Aug 1, 2013
I have numbers in 7 groups in cell B2:H8 and winning numbers in cells B11:H27 The function below works fine with: For example if I select cell A2 (G1) numbers are in this row in the cells B2:H2 highlight perfect in the winning numbers in cells B11:H27 As shown in the picture below
A
B
C
D
[Code]....
View 6 Replies
View Related
