looking for a way to look in a column for a value, and then spit out the highest value in corresponding cells in a column further over
so i want to look at all the classes associated with the same case, and have excel spit out the highest CscR no associated with those cells that do match..
yes i could do it manually, but my actual file has like 4000 samples in the first column.
I'm trying to figure out how to count the number of unique countries where there is an office.
So pretty much, I want to count the unique entries in Column A where there is an X in Column C.
I would like to sum uniques across worksheets. Below are the formulas that Domenic provided me and it works great but I could not modify the formula below that is in red to sum uniques instead.
=SUMPRODUCT(--(RIGHT(A3:A37,5)<>"Total"),--(MATCH(C3:C37&D3:D37,C3:C37&D3:D37,0)=ROW(INDEX(C3:C37,0))-ROW(A3)+1),B3:B37)
Define Names:
ArrayA:
=COUNTIF(OFFSET(INDIRECT("'"&Sheet1!$C$2:$D$2&"'!A3:A100"),ROW(INDIRECT("3:100"))-3,,1),"<>*")
ArrayB:
=N(OFFSET(INDIRECT("'"&Sheet1!$C$2:$D$2&"'!"&Col),ROW(INDIRECT("3:100"))-3,,1))
Col:
=CHOOSE(Pos,"A3:A100","B3:B100","C3:C100","D3:D100")
Pos:
=TRANSPOSE(MMULT((T(OFFSET(INDIRECT("'"&TRANSPOSE(Sheet1!$C$2:$E$2)&"'!B1:D1"),,COLUMN(INDIRECT("B:D"))-COLUMN(INDIRECT("D:D")),,1))="Net")*(COLUMN(INDIRECT("B:D"))-COLUMN(INDIRECT("B:B"))+1),TRANSPOSE(COLUMN(INDIRECT("B:D"))^0)))
=SUM(IF(ArrayA,ArrayB))
I want something to give me the total of different names in column A, I know the total is 8 but if I add another name I want Excel to add it up automatically in cell A2 in sheet #2.
View 9 Replies View RelatedI have a list of names in a column that will be changing on a weekly basis (with some names repeating). I need to create a Named Range that only counts unique values from that list of names (dynamically changing every week).
View 11 Replies View RelatedI have about 160 rows in collumn A. I want Excel to count them, but ignore blank ones and repeated ones. How can I accomplish this using macro?
View 5 Replies View RelatedOnce I have used this code to filter for unique item(s)
Sheets("Source Data"). Range("Data").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Sheets("Source Data").Range( _
'"Criteria"), Unique:=True
Sheets("Source Data").ShowAllData
As this filter searches for unique value there is only 1 row of info. I then want to select specific (1 or 2 cells) info from this row and the make this cell(s) = to a cell on another worksheet. As this unique row might be 199; I don't know how to reference it using VBA. I know what columns I need but cannot determine which row it will be in. Assist as I have looked at various threads w.r.t. duplicates/autofilter/search but cannot seem to get the correct code.
I have this formula below it will list uniques from column A but I would like it return in a alphabetical order. Is there a way to add that condition in the formula below a all in one formula?
=INDEX($A$3:$A$23,MATCH(TRUE,ISNA(MATCH($A$3:$A$23,C$2:C2,0)),0))
Example:...
I would like to get a unique list of letters from multiple columns.
I tried to use this formula below but it does not work with multiple columns....
I use this formula to count uniques in Column I if they started with "P" :
=SUMPRODUCT(($I$2:I554"")/COUNTIF($I$2:I554,$I$2:I554&""),N(LEFT($I$2:I554,1)="P"))
Now if I add 2 more criteria it gives a wrong result" :
=SUMPRODUCT(($I$2:I554"")/COUNTIF($I$2:I554,$I$2:I554&""),N(LEFT($I$2:I554,1)="P"),N($F$2:F554=F555),--($G$2:G554""))
as 0.0625
I have a list with many duplicate names in it, from which I only wanted 1 instance of each name, to be placed in another sheet. So, I recorded a macro that selects, from the sheet called "Data", column E (the column with the relevant list), copy/paste's it into sheet 3 in column A, selects the new column, and runs an Advanced Filter command on it, to identify and display all the unique names in it in the same place. It ran just fine when I recorded it. But then, when I deleted the data created by the macro and tried to re-run it, I got random data. I found that whenever I click on a different cell, it provides a different output. I only want it to select Column E, and copy paste that, not other random columns depending on which cell is currently selected. This is my
Sub Fetch_Unique_Names()
Sheets("Data").Select
ActiveCell.Range("A1").Select
ActiveCell.Range("E1:E2200").Select
Selection.Copy
Sheets("Sheet1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.Range("A1:A2176").AdvancedFilter Action:=xlFilterInPlace, Unique _
:=True
End Sub
I have attached a sample sheet. Can someone be kind enough to teach me how to count unique numbers of visible cells ( I would be applying filter in the table) in textbox 1; and subtotal of the visible list in textbox 2?
Right now the range to count unique numbers is set to A2:A15, but I want to be able to change that range to other ranges, like B2:b100, etc. I do not know VB.
I am designing a userform that involves a listbox that I am having trouble with. Please note I have little experience with vba, so I don't understand most codes.
I want to make a mulitcolumn listbox, populated with the attached pivot table. I really do not know where to start, and there is no "populating multicolumn listboxes for beginners" instructions anywhere on google or in here. At least, none that I understand.
What I am trying to create is a 2-column listbox, where in the first column, the "code" fields are shown, and in the second column, the corresponding " Name" fields are shown. In the pivot table, you can see how several routes fall under a service, and several services fall under a pc, etc.
I have one sheet "FF" which shows categories of companies (1-48) and each of these categories include several SIC numbers.
What I now want is to find the right category (1-48) for each SIC code in my second sheet.
is it posible to use lookup to complete a name range?
example:
if "joe" is a barbarian, sorcerer, druid, each class has features dependant on its class level, not the character level. so, if "joe" were a 9th level barbarian, 7th level sorcerer, 7th level druid, his character level would be 23.
each class is assigned its own name for its total levels. char_lvl_barbarian, char_lvl_sorcerer, char_lvl_druid.
is there a way to use lookup to match char_lvl_ to a given class type or do i need to type each individual class type level name range? i'm hoping for one formula that can allow me ease of use instead of a multitude of conditions.
I need to determine the shipping price of, say, a fruit based on its weight. I'd like to do this by looking at a matrix that lists fruit type (column E), weight range (column F/G), and resulting shipping price.
View 8 Replies View RelatedTimeForce
00.2
0.50.5
11
1.52
24
2.54
34
3.54
44
4.54
54
5.54
64
6.51
70.3
for example i have this data I want to average range of some values in force column for which i only know range of values in TIME column..
For Example I want to average the values for which the values in time column are 0 to 1.5 ((0.2+.5+1+2)/4)
I deduct a formula but it is giving error AVERAGE(LOOKUP(0,A:A,B:B):LOOKUP(1.5,A:A,B:B))
I have two sheets in Excel as below format. I would like to lookup (fill) “issue to” column of sheet2 in respect to sheet1. Please is there any funcations?
Sheet1
chqno Fr chqno toIssue to
1000110050Branch-1
1005110100Branch-2
1010110150Branch-3
1015110200Branch-4
Sheet2
Chq noIssue to
10002
10006
10155
10120
10008
10035
10151
I am creating a list with Data>Validation>Allow>list and it requests the source for the list- all OK, but the list produced needs to depend on other factors, I have several named range's containing different lists.
I need to insert in the "list Source" box a cell reference, which contains a the name of the range ie "list46" and for the source to recognise this as a list to produce
Is it possible to lookup a named range to produce a list?
Sorry about the explanation very difficult to put in to words!
Is there a way that I can do a vlookup in 1 file and specify more than 1 data range to lookup the data in?
In this case I have one file to put the vlookup in and a second file with more than 1 tab and I want to have it search each of those tabs and return the result. The format of these sepatate tabs are to each other and for that matter, identicle to the main file.
I am creating a product feature catalog. I have created a dropdown with the names of the products. There are many details like price availability etc.
Now I have the product features in another sheet, something like this:
Product Name
Product Features
Product A
Good Battery Life
Product B
Big Screen
Product A
Bigger Processor
Product C
Low power consumption
I have selected the entire table and named it as "Feature".
How can I display the entire table by using the NAME feature i.e. using the reference "Feature". When I try to use =Feature i get the #VALUE error.
I would like to match a value between a weight range.
For example:
0-0.5 gram is $2
0.5-1 gram is $3
So, if my weight is at 0.3, the formula should give $2.
I have a list of names in sheet1 column A and 3 more list of names on sheet2, sheet3 and sheet3. What I'm trying to do is find a match for each name on sheet1 column A on the other sheets and depending on which sheet it was found, I need a specific value returned to sheet1 column B.
For example, if A1 was found on sheet2, then on B1 I would need to return the value "Morning" but if it was found on sheet3, then I need the value "Afternoon" and if found on sheet3 I need the value "evening" returned to B1 and then the same for every other name on the list found on sheet1 column A.
What I tried was this.
This goes in Sheet1 B1 (then I would drag it down to search for the rest)
=IF(AND(MATCH(A1,'sheet2'!$E:$E,0)),"Morning",IF(AND(MATCH(A1,'sheet3'!$E:$E,0)),
"Afternoon", IF(AND(MATCH(A1,'sheet4'!$E:$E,0)),"Evening","Un-assigned"))
Now the problem with this is that it only works if there is a match on sheet2 but if there is no match, it just returns "N/A" and it does not move on to sheet3 and/or sheet4.
I also tried with vLOOKUP instead of MATCH and the same thing happens.
I also tried this and the only thing I get is "Un-Assigned"
=IF(AND(A1='sheet2'!E:E),"Morning",IF(AND(A1='sheet3'!E:E),"Afternoon",IF(AND(A1='sheet4'!E:E),"Evening","Un-Assigned")))
I'm trying to set a range to use in a Lookup function but it's not working. I've done this other times with no trouble and I don't see why it's not working now.
If Target.Row > 2 And Target.Count > 1 Then
For Each c In selection
If c.Column = 11 Then
' confirm that Activity ID is in the Reference file list
Set Lookup_Vector1 = Worksheets(c.Worksheet.Name).Range("Activity_ID")
Error occurs on the Set Lookup_Vector1 line, Run-time error '1004': Application-defined or object-defined error.
The range Activity_ID is defined in the current worksheet but exists in another workbook. The c.Worksheet.Name correctly gives the name of the worksheet where the range is defined.
I am trying to display part of a worksheets new sheet based on some criteria. I have a worksheet with a list. I have a date field in the list. I need to breakup the into several sheets based on the dates. I want all of items in the list with a February date put onto a tab titled February and all items on the list with a March date on a tab titled March. I haven't been able to find a funtion or code that will do this. The new tabs don't have to be created through automation. i can create those manually. i am looking for a formula or possible code to put on the new sheets that will look up and return specific rows from the main worksheet with the master list. i attached a sample of my worksheet. I need to search by Column O titled Date of Next Review.
View 4 Replies View RelatedI'm using an Array formula to lookup a particular value then return a related column value.
What I would like to do is Sum a related range of cells.
The formula I have used to look up the single cell is
{=INDEX('[PL.xls]Parts List'!A4:S205,MATCH(A63,'[PL.xls]Parts List'!A4:A205,0),7)}
So in this case the related value in column 7 is returned.
I would like to sum the related values in say columns 5 to 11
I have a workbook where road structure data is recorded i.e. bridges, culverts, etc.
One of the worksheets has data I need to lookup. The primary worksheet called “Structure_Data” has the ‘Road Number’ (RN) of a particular road and the ‘Road Running Distance’ (RRD) of a particular structure. A particular road may have many structures along its length.
Another worksheet called “Road_Number_of_Lanes” has data that that indicates how many lanes there are between particular RRDs.
Here is what I am trying to work out...
In the “Structure_Data” worksheet look at the RRD of a structure and the number of the road it is on, then look up the road number on the Road_Number_of_Lanes worksheet; look at the ‘Start RRD’ and the ‘End RRD’ and return the number of lanes for that particular RRD.
“Structure_Data” worksheet
Column C has the Road Number
Column X has the Road Running Distance
“Road_Number_of_Lanes” worksheet
Column A has the Road Number
Column C has the Start Road Running Distance
Column D has the End Road Running Distance
Column E has the number of traffic lanes
My problem is that for example Road Number 1000 starts at 0 and goes to 927.09 (approx 927 km long). There are 27 entries of RN 1000 i.e. from 0 to 411.69 there are 2 lanes, from 773.62 to 774.85 there are 3 lanes and from 774.85 to 778.09 it goes back to 2 lanes.
How can I find out how many lanes of traffic there is at a particular structures location.
How to lookup for greater than a given particular value in a list of data? Suppose the value to find is in cell C17 & the column to be looked for is F, in the attached sheet. Now the data range is from F8:L27. If the value in C17 is not present in Column L, then the value greater than the specifed value should be searched in Column L. I have tried using VLOOKUP, but cannot find for a range of values.
View 2 Replies View RelatedI have two values I need to find in a chart.
(See Attached)
I can do a Vertical Lookup but don't know how to search for the second value to identify how much to offset to retrieve the value.
THE FORMULA - IT FINDS THE LAST-OCCURRING NUMBER IN A ROW, AND ADDS 1 TO IT IF A CELL LOWER DOWN IN THE PRESENT COLUMN CONTAINS TEXT:
Here is a formula to find the previous occurrence of a number in Row 1, and to ADD 1 to it if in the same column (Column Z) as the present cell (Z1), there is NOT a blank in the specified cell (Z5)
=IF(ISBLANK(Z5),"",LOOKUP(10^100,$C1:Y1)+1)
This is quite nifty. Basically if Z5 says "go!"
then Z1 will find the last number in Row 1
- let's imagine F1 contains "300" as the last number so far in Row 1 -
and add 1 to it
- in this case Z1 will thus say "301"
THE NEXT CELL:
With each next cell in Row 1, the formula moves up an increment, except where specifically told to freeze ($). Thus Cell AA1, the next cell in Row 1 after Z1, will have the formula:
=IF(ISBLANK(AA5),"",LOOKUP(10^100,$C1:Z1)+1)
THE PROBLEM:
I don't want to extend Row 1 beyond Column Z. I want my spreadsheet to be more compact. So l want to continue the formula on another row. Let this be Row 43.
So, how do l set the formula to LOOKUP the last-occurring number in Row 1 between Column A - Column Z (i.e. $C1:$Z1) but then - if no number is found in that range - continue the search in Row 43 between Column C - Column F for example (i.e. $C43:F43)?
And how many times can l introduce a new row into the range within which to find the last-occurring number?