I have a excel sheet with a macro set up. On the main sheet is all the information with expiry dates. Now each entry according to the expiry date goes into another sheet (Monthly Sheets) in the same document. However if you add to the main list and press go it re-sorts the list and duplicates are put into the monthly sheet. (Hope that makes Sense)
Here is the code from Naras for the sorting:
I have in "column A" an accountnumber and in "column D" a lastname.
The lastname can appear more than once and the accountnumber also.
Though I want combobox1 on userform1 to contain the lastnames.
It's possible to contain a double lastname as long as the accountnumber isn't the same.
For instance:
accountnumber: Lastname:
1234 Johnson
7890 Johnson
here Johnson will be insert twice into the combobox.
accountnumber: Lastname:
1234 Johnson
1234 Johnson
I´working on an excel sheet where i´m copying a range to another place. So far so good. Then I want to remove all double entries for each rows in the new range. I managed to get a code working for one row. When I want to loop it for all the rows in the copied range, I get a an error popoing up when the loop starts working on the second row. Error 457: "This is already associated with an element of this collection" The line creating this error is coll.Add cell.Value, CStr(cell.Value)
Sub Sortere_StederBeta()
Dim coll As New Collection
Dim lcount As Long
Dim cell As Range
On Error Resume Next
Range("B4:U33").Copy
Range("B36").Select
Selection.PasteSpecial Paste:=xlPasteValues
For i = 36 To 65 Step 1
Range(Cells(i, 2), Cells(i, 22)).Select
Set coll = Nothing........................
The problem seems to come from the fact that the Coll (New Collection) is not reseting for the next loop. I tried to set the Coll to Nothing but doesn´t have any effect.
=COUNTIF($A$1:$D$100,A1)=1
however I don't know how to change that to suit my needs (or even if it is at all possible to change it to suit my needs)
I will try my best to explain how my spreadsheet is set up as unfortunately I am at work and I can not use the method to show you the spreadsheet.
My spreadsheet:
I already have Data Validation in use from a list that I created. I use the same validation list in Columns C, E, G, I, K-BA
I also use validation list in other cells, and it is OK if these are duplicated throughout the row.
The only cells that should not contain duplicate information are the ones from C, E, G, I, K-BA
Basically I want to stop someone accidentally chosing the same option from the list within the same ROW.
Just in case it makes any difference I should let you know that I have already had help from the forum regarding macros for this same spreadsheet, here is the macro that is currently running just in case it makes a difference if there is an answer to my current problem.
Option Compare Text 'A=a, B=b, ... Z=z
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
Dim Rng1 As Range
On Error Resume Next
Set Rng1 = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, 1)
On Error GoTo 0
If Rng1 Is Nothing Then
Set Rng1 = Range(Target.Address).........................
I want to use (VBA) data vaildation to restrict duplicate entries.
Worksheet Name = "Metal Type"
NoDupe Cell = B15 (allowing text and numbers)
Range to check = (Sheet)="Metal Type" (Range)=A21:A100
Error = Message box "Enter Unique Name in Cell B15"
Then Exit Sub
How do I stop a pivot table adding data together?
e.g.
1st Nov -100
1st Nov - 100
2nd Nov - 200
2nd Nov -200
I want this but instead get this:
1st Nov - 200
2nd Nov - 400
I need the pivot to split it out,
my excel sheet runs through a lot of calculations, opens Flowmaster, a simulations program, passes on data, receivs data and so on. Is there any way to have a user input to stop the whole simulation. During the first tries I had a lot of break point in my debugger. But now I want to have a button to hit or better just some keys to hit to stop it without using the ctrl+alt+del which closes everything.
View 4 Replies View RelatedWhat I need to do is sort certain entries in longer list (column A, it is in .csv format and needs to be in it so coordinates and names and ID, all sorted with commas) and I have another list (column C) which is shorter list of certain IDs. I googled and tried and got some results for the basic structure but the fuction seems to fail. It doesn't matter how I get that third list done, but there is only one criteria: since the list in column A is really long and those entries need to keep the .csv formatting, the function should copy that info what is in the matching cells.
Let me try to put it simple: .csv cells from column A that have matching ID from column C should be copied to column B (or N).
the function I'm working with right now is
=IF(ISERROR(FIND($C:$C; A1))=TRUE;"0";A1)
I have a list of data and I want to identify the unique entries for both columns but the second column has to unique to the unique values in the first column.
Example List
Fruit
Color
Apple
[Code]....
Let me show you how my sheet is set up.
Steeves 10 ny
Steeves 15 ny
Steeves 20 nv
Steeves 5 tn
Steeves 10 ca
Keppler 16 tx
Keppler 20 nj
Now for this example I need to sum column B if A=Steeves and C=NY.
In this example it would return 25.
Is it possible to enter double inequations? Excel returns "false" even it is obviously true. If I type =1<2<3 the result is "false".
I can get the correct result using =and(1<2,2<3), but that makes it longer, and if I have more formulas inside instead of numbers I'll have to repeat the middle one, it will take the place, but only in oreder to compare it again.
Is there anything that can be done with this?
On one sheet I have an array of 4 cols. It looks like this:
123 Chris 12345 Jones
123 Chris 23456 Pete
123 Chris 34567 John
On the main sheet there are also 4 cols. In cell A1 I enter a number, in A2 I have a VLookup to get the corresponding name for the number entered in A1, which works fine, I type 123 in A1 and get Chris in A2.
In a perfect world I would like A3 to become a dropdown with 12345, 23456 and 34567 so the user could then select one of those, and by doing so, A4's VLookup would give Jones, Pete or John.
I don't think a dropdown can be populated this way - am I stuck using VBA?
I have a list with fractions such as 25 1/4, 33 5/16, 20 15/16, etc. i have them formatted as FRACTIONS 2 DIGITS 21/25. Manny of those have double spacing ex: 25 1/2, So i am wondering if theres a way to take the extra spacing off the ones that have double.
View 10 Replies View RelatedI am attempting to create some code that has 2 loops in it.
THE SITUATION:
I will have 2 ranges (eg B54:B100, and B106:B149 - the problem is I will arrive at these ranges by code such as: Range(Selection, Selection.End(xlDown).End(xlDown)).Select)).
WHAT I THEN REQUIRE
I need to have some code that will look through each cell in the second range and if the cell contents is found in the first range then the entire row from the second range will be deleted.
I have a double vlookup problem here.
Here's a simple illustration:
A B C D E F G
1 1 1 1 One
2 2 2 2 Two
4 4 3 3 Three
3 3 4 4 Four
5 5 5 5 Five
I need to match A,B with E,F.
If they match, I need to display the string in G in C.
=INDEX('sqft - PC each bldg'!D2:D1705,MATCH(Sheet5!B3&Sheet5!F2,'sqft - PC each bldg'!A2:A1705&'sqft - PC each bldg'!C2:C1705,0))
my result is N/A
I wonder if it has to do with both lookup ranges being columns?
Have a quick one, trying to figure out a simple expenses/income/saving tracking table for my wife and in-laws; have figured out most of it. Will use pivot to extract most of the data, but on a summary sheet I need double condition sums (or at least I guess so...). For example I have "amount" column (which is expanditure) and date column (have used function to extract month and year for other purposes to separate columns), how can I calculate multiple costs (all the costs/expenditures in a month) that have taken place in eg. april 2008 only/ since I want to display it on a separate sheet as a summary overview...here's the link to imageshack, dunno if it's gonna work:
[url]
Have tried something like: =SUMPRODUCT(month="4")+SUM(amount) but it sums up the entire column...
or this one: =SUMPRODUCT(ISNUMBER(MATCH(4,month,0)+SUM(amount)))
I know I miss a bunch on the last one
I have a list like this:
xxxxxxxxxx1-jan | 2-Jan | 3-Jan
-------------------------------------------------
Location 1
Bob Smith ----B2--- C2 ---- D2
Frank Thomas-B3---- C3---- D3
Location 2
Bill Clinton----ETC ALL
George Clinton
And another sheet, a similar list, (just names and randomly sorted) but populated with the values I want,
xxxxxxxxxxx1-jan | 2-Jan | 3-Jan
-------------------------------------------------
Bob Smith --- 2 -----5------ 1
Frank Thomas 3----- 1 ------2
Bill Clinton ----4----- 5 ------2
George Clinton 4----- 4 ----- 4
I want to check the name in A2 on the first sheet against all of column A on the second sheet, then find the number value for the correct day for that name.
How can I write a formula that will look up these values for me? I looked at OFFSET but unfortunately I don't have a lot of excel experience (yet)
I have a calendar (tab March in the attached file) where I can indicate the delivery package of tab List. The problem that if the delivery happened twice a day, only the first one will be indicated. If there is any way to indicate both deliveries?
View 2 Replies View RelatedI am trying to avoid double counting two separate rows with the same columns. For example:
StockFund AFund BFund CFund DFund EFund FFund GCountDesired Count
LKOH111100044
LKOH001111042
If I have two stock codes the same in column A (not necessarily one below the other), I only want to count one of the occurrences per fund. So I would like to avoid counting the duplicate records for funds C and D. The desired outcome for the total holdings of LKOH should be 6 and not 8.
I am trying to lookup the value that corresponds to three sets of data. The formula I have been using is but I am still getting #NA. I’ve attached the spreadsheet
=INDEX($B$3:$I$17,MATCH($B21,$A$3:$A$17,0),MATCH($A$22,$B$1:$I$1,0)+MATCH($B$20,$B$2:$E$2,0)-1)
I am looking to search Vendor name 1, Actual and A to return the value of 1.
I am trying to write some questions on cells of Sheet1 of a workbook. Then I want to write the answers on cells of Sheet2 of the same workbook. I want the user to be able to double click the question-cell on Sheet1 and be taken to the respective answer on Sheet2.
View 6 Replies View RelatedI have a spreadsheet that just uses the basic "SUM" function. This morning I went to use it and the function does not work after I change a number within the field of that function. If, however, I go into the SUM function and double click, then hit enter, it does the new calculation.
View 3 Replies View RelatedTrying to do a double lookup, or a conditional lookup. Attached spreadsheet will provide much more clarity. I have multiple data sets within a universal file, each data set can contain the same constituant therefore I would like to lookup/vlookup a value only if the constituant is part of the data set I want to pull from.
In attached sheet: if cells in Col A= "F", then vookup("RDT",(array would be data set "F" (I think)),return value in col 2 (assuming vlookup is in col B), t/f)
I'm trying to simplify some data entry on an excel sheet, by making a double click vba to add a time any where in 2 columns, specifically H and I .
[Code] ....
I tried to modify this code but my understanding of VB is very lacking.
I have a timesheet which for certain days of the year calculates double-time. What i would like to do is have a formula that gives double time for a 24 hour period. I have a formula setup with a start time and end time for my double time, but it wont stop at midnight.
View 11 Replies View RelatedI have a table of data with two rows of unqiue identifiying information. I am trying to develop a formula that will allow me to do a vlookup twice, once for each row of unique identifying information to retrive the value in same row for the third column. For example:
apple red 50
aaple blue 75
aaple yellow 80
apple green 90
grape red 12
grape blue 17
grape yellow 45
grape green 41
So, I am trying to do a lookup where I can say what is the value in the third column for apple and red. Or, what is the value in the third column for grape and green.
I have this list of all rangenames in my workbookin column A:A
For some reason I want to doubleclick on the rangename in the list to activate this range.
The reason is that I want more smooth activation-prosess; The Excel Range List is too long to practical use as a direct lookup, so I want to activate directly from my own list (where i have added definitions to the range names.
I have tried to build a hyperlink formula, but failed. Maybe the macro below can be modified to do the job, but I am stuck here as well.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim RangeName
Application.ScreenUpdating = False
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A:A")) Is Nothing Then
Cancel = True
[Code] ...........
I am trying to create a VBA code that will search through a column"A" and if it finds a calue that C1 then color it, when it finishes searching columnA then loop again and search for items that match C2
Here's what I have so far with no success
Sub ColorItems()
Dim i As Integer
Dim j As Integer
Range("C1").Select
For i = 1 To Selection.CurrentRegion.Rows.Count
For j = 1 To ActiveCell.Offset(0, -2).CurrentRegion.Rows.Count
If ActiveCell.Offset(0, -1) = ActiveCell.Value Then
With ActiveCell.Offset(0, -2).Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
I have a spreadsheet with two drop downs, one lets say has country and the other city. When I select a country in the country drop down, I want to be able to only select cities in that country.
I used a method that I found in an earlier post, where I define the name of list of cities of a country to that country and then use the indirect function in city dropdown down validation. The prolem is I can still get mismatched city and country because when I change country, I still have the city from the previous selection untill I drop down and select the city again.
I would like the city to show blank when I change the country until I select a city from that country.