In A1 to A10 I have numbers 1 through 10.
In B1 to B10 I have some of the numbers listed from column A, but not all, and in no particular order.
In column C, I would like to list only numbers from A that are not listed in B.
I want to create a list of names where the name is multiplied by the number on the second column. See below example.
NamesTickets EarnedMark5Hazel2Art4
On the other sheet's Column A
MarkMarkMarkMarkMarkHazelHazelArtArtArtArt
What macro can I use?
I know how to use array formulae to create a unique list, i.e.{=INDEX($G$1:$G$760,SMALL(IF(ROW($G$1:$G$760)=MATCH($G$1:$G$760,$G$1:$G$760,0),ROW($G$1:$G$760)),ROW()))}
however this is giving all the unique items from column G and I only want the unique items that have a non-zero value in column H as well. This would be the sumif of all instances that would have to be zero. I've tried to crack it and I've tried to search for solutions but so far no joy.
I have a list generator that creates a set of data in a multi-column & row dataset. I would like a formula to create a list of the alpha data points only which excludes blanks and any errors.
Example:
AADD
#N/A
BB23EEFF
#N/A
#N/A12GG
CC
Results:
AA
BB
CC
DD
EE
FF
GG
I'm creating a spreadsheet to keep track of my costs of production in an online game. Within the game there are a range of spawned resources that appear for only a short time before being unobtainable these resources have specific types that is shared between multiple spawns of the resource but each resource spawn has a unique name.
My first worksheet lists all the resources and their various qualities and the later worksheets are meant to allow me to choose from a list resources matching the requirements of the item I'm looking to craft. The example i have shown in the second picture requires Tatooinian Fiberplast and Lokian Wild Wheat to craft so in the Chosen Resource column I would like to have a drop down list allowing me to select the named resource type i would like to use - for Tatooinian Fiberplast the only thing on the list should be Omnitwixi and for the Wild Wheat it should show Fizi and Krad
[URL]....
[URL]....
I am aware there are people with more pressing problems than computer games and as such
I'd like to create a drop down list in data validation from a column of data that contains numerous duplicates.
For example, let's say column A contained hundreds of transactions with either North, South, East and West, how could I create a drop down list in another cell that only had four selection options?
I need to create a dynamic list from a table of data.
I have performance data for 110 different pumps. Data points are generated every hour, and the table is updated with new data periodically. I want to automate the population of a list of 6 different pumps, and specify the date range populated. To put it another way: I want to place data from Pumps 1 through 6 for all of November into Columns A through F. Then I want to clear that data, and show data for Pumps 105-110 for last week in those same Columns.
One of the main goals of doing this -- other than quickly narrowing a field of data -- is being able to quickly chart this data on a scatter plot with a custom format. The pumps are grouped together by region, and individual pumps have specific purposes. So I need to quickly generate graphs with a series' color scheme or formatting that is consistent and logical between different pumping regions.
I haven't been able to make pivot table work because of the graphing issue, and also because of the way it handles data points and presents data. I would like to make this work with excel functions and maybe some filtering, but I'm not opposed to figuring out a VB script if you think that's what I should use.
1 workbook, 2 worksheets (or tabs). On tab 1, I want a formula/alert that tells the user if any duplicate values exist in Column A of tab 2
Tab 2, Column A, has Unique ID's (6 digit numeric values)
The user manually inputs the ID's on new rows in Column A
Row 1 is reserved and in use for something else
Row 2 is my header, so cell A2 says "ID"
Row 3-623 currently contain unique ID's
When the user inputs a new ID into cell A624, then they return to Tab 1, I want my formula/alert on Tab 1 to tell the user that they have duplicates in Column A of tab 2. I know the Conditional Formatting, but if the user copies in 100 new values, they won't necessarily see the highlighted cells. My tab 1 is my "checks and balances" and the last place the user is suppposed to look to ensure that they haven't created any duplicate ID's. If the user sees a warning message that says duplicates exist, then I'll tell them that they need to look at column A (for cells that have been conditionally highlighted).
One issue that I'm running into with the conditional highlighting is that I want cells A3:A1048576 to already have the conditional formatting - this way when the user inserts a value into Cell A624, then A625, etc they conditional formatting is already there. Right now with data in cells A3:A623, cells A624:A1048576 are all highlighted with the Red/Bold Red Font (which is okay I guess), but ideally it would be nice to not count 2+ empty cells as duplicates and I'll have to have my formula on Tab 1 not include the blank cells.
I DO NOT want to use the Remove Duplicates feature of Excel 2010. If I remove them I could be removing data in columns B, C, D, etc that belong to the Unique ID. I just need the user to be told in Tab 1 that they DO have duplicates and I'll train the user how to research this and fix it.
The reason I want to look for duplicates in the entire Column A is because the list of Unique ID's will grow over time.
I have a list of data that currently has a conditional format on it of =COUNTIF($F$2:$F2,$F1)>1 so that it will highlight the duplicate but keep the first entry blank. I wondered whether there is a way to identify the last duplicate in the list. i imagine this could be done in a column say with an "L".
View 7 Replies View RelatedI am trying to set the below table up as a validation list. I am having issues with putting it in as list as it creates a new line every time a “,” (comma) occurs. I know I can use the custom and pull the list from a table, but I can’t use this option (the reason I can’t use the table, if interested, is because the single sheet with the validation contains about ~35,000 line items that are being split up into ~200 different sheets. Each sheet is then sent as a separate email…the problem with using the table is that it would on a different spreadsheet to begin with. The email only sends one sheet. As such, it would no longer be able to reference the table).
A - Less than $25,000
B - Between $25,000 and $49,999
C - Between $50,000 and $99,999
D - Between $100,000 and $249,999
E - Between $250,000 and $499,999
F - Between $500,000 and $999,999
G - Over $1,000,000
I have a large list of in rows with multiple column headings: ie. Name, date, amount. I need to generate a separate list whereby the rows that correspond to a given criteria are extracted and listed sequentially.
I have attached a simple example to demonsrate what i mean.
in the attached spreadsheet, i have a column of colours, and a column of objects- RED book, BLUE hat, etc.
I'd like the criteria that i type in the green cell (a choice of colour), to generate a list of all the items that match that colour, and be displayed in the orange cells. So, if the green cell says BLUE, the list should pick out the BLUE dog, BLUE hat etc.
I'm trying to create a worksheet register. Insofar, with the help of this forum, I have been able to put together a code that creates a list of hyperlinks to all current worksheets
Sheets("Register").Select
Dim wks As Worksheet
Dim rngLinkCell As Range
Dim strSubAddress As String, strDisplayText As String
Worksheets("Register").Range("A3:A600").ClearContents
For Each wks In ActiveWorkbook.Worksheets
Set rngLinkCell = Worksheets("Register").Range("A600").End(xlUp)
If rngLinkCell <> "" Then Set rngLinkCell = rngLinkCell.Offset(1, 0)
strSubAddress = "'" & wks.Name & "'!A1"
strDisplayText = "" & wks.Name
Worksheets("Register").Hyperlinks.Add Anchor:=rngLinkCell, Address:="",
SubAddress:=strSubAddress, TextToDisplay:=strDisplayText
Next wks
In additon to this I would like an addition to the code to pick up data from a cell (G10) within all the worksheets and place it in Coloum B (starting from B3) of the register. And clear the coloum firstly to make way for any updates. I tried doing this myself by modifying a recorded macro but the data would just write to one cell over and over again
I am facing problem in finding the second large value while my list is having duplicate data.
For Example
my numbers are:-
12,65,45,12,87,90,65,87
if I want to find out the top four value by using the formula:-
=large(A1:A8,{1;2;3;4})
the result is coming like: - 90,87,87,65
instead of this I want the result as: - 90,87,65,45.
I have a spreadsheet that needs to have a validation drop down list in three cells in every row (D, E, F). There are three items in the list, say, "One", "Two" and "Three".
My only problem is that there can only be one entry of "One" in each row.
I want to put a drop down list in Cell D3 with the list of names from column B. There are 14 different names, but when i try and create the list it gives everyname from Column B.
Also once the drop down list works i would like the table to be populated with the managers supplier, so if Dan was chosen in D5, Suppliers 1 to 9 would appear below D3.
I am trying to create a list of residents, unit types, and unit numbers. I have 3 problems with the following code.
1) The code ignores sheets that have no value in the specified cell. I need it to return a blank cell for those in order to keep the data in each column matched with the sheet it came from.
2) The code includes data from hidden sheets. I only want to list data from unhidden sheets. And more specifically, I want to omit data from unhidden sheets that have their tab colored black.
3) The code includes data from the sheet named "Totals" which I thought I was telling it to ignore.
I have forms filled out by many, and I need to compile the information in it to create a list in my summary.
Is that possible?
fortunatly, the information always resides in the same cell on every file.
Sheet11
EFGHIJK8480NVF-25747NVF25747
8581ATT-67546ATT67546
8682RTC-35615RTC35615RealRTC-35615
8783CSX-45715CSX45715RealCSX-45715
8884CSX-24915CSX24915RealCSX-24915
8985HTC-56947HTC56947
9086MCAS-85415MCAS85415RealMCAS-85415
9187BNSF-77716BNSF77716
9288MCAS-97316MCAS97316
9389CLRV-24760CLRV24760
9490CSX-24101CSX24101
I have created a Data Validation list in one of my spreadsheets which works fine. The list itself has no duplicates which is fine but when I select my entries in the other spreadsheet, I want to make sure they cannot select the same value twice.
I tried selecting "List" in data validation and using an AND with COUNTIF but it doesn't work.
=AND(Employees, countif($A$1:$A$20,A1)=1)
I need macro that will check for list of duplicates in sheet1 and place the duplicate data in sheet2.
I know how to check duplicates in only one column.But now my sheet having lot of columns.
a
b
c
d
1
monday
tuesday
wednesday
thurs
2
ted
bill
frank
mary
[Code] .......
We use excel for scheduling purposes and each and every day we fill out a floor chart with who is working that day. As it stands now we have to print out a template and hand write all names or type them in and print it out. What I would like to try and do is define each set of names and allow for easy access to auto fill these names. I feel it needs to be a simple process as not all of our managers experienced in excel or formulas. What I would like to try an accomplish is in sheet 1 cell A1 to be able to create a drop list or formula that that would let me return all the names for a given day.
For the purposes of this discussion we can just define each list as the day that is the header ( IE A2:A4 on sheet 2 would be defined as "monday". Also, I already have the names autofilled based off of what the our schedule has. I'm curious if I could create a drop down list with each day of the week as an option, and if they select Tuesday it will then return all the names defined as Tuesday in a column below.And the next day be able to return Wednesday names and so on.
I'm not stuck on it being a drop down list. Maybe if they could just type and =(defined name) in the box above and it would return all the names.
I have tried an =(defined name) and it does return the value but only for the first cell and it has to be in the same row for it to return the name. Which doesn't work because I would like the names that are being defined on a separate sheet.
I've got two long columns of dates and names. The dates and names can repeat. Eg:
01/01/14
Fred
01/01/14
Joe
01/01/14
Tom
[Code] ........
I want to input a date and create a list of names with only the names from the date I enter and earlier, and excluding names that appear again with a later date.
E.g. if I enter 01/01/14 I'd get:
Joe
Tom
But not Fred, as although he appears on 01/01/14 he also appears with a later date too.
I have a column with values, say column a. In column a are several rows with values equal to zero. I would now like to create a new list that omits the values that are zero, e.g.:
original list:
23
0
54
76
0
0
new list:
23
54
76
I want to get the list of days which are only weekdays ( excluding sat, sun but includes holidays during weekdays) I have tried WORKDAY function but it is not exactly i want coz it excludes holidays during weekdays as well.
View 5 Replies View RelatedI have 6 worksheets in my file. In sheets 1-5, column A2:A26 list people's names. Some people's names appears on more than one sheet. Not all cells are populated with a value.
ex.
SHEET1
COLUMNA
Bill
[Code]....
My attempt was... =INDEX('Week1:Week5-!$A$2:$A$26,MATCH(0,COUNTIF($A$1:A1,"Week1:Week5"!$A$2:$A$26),0))
where the sheets were Week1-Week5 and the values on each sheet was A2:A26. But I think there's an issue with Excel being able to 3D reference for these types of functions.
I'm making an attendance list that calculates regular hour and overtime hours for days in a month on rows. Besides the number of hours, there's input for v and s, which stand for vacations and sick days. I'm using the "if" function to separate hour and overtime, and when I put v or s in a cell it messes up my totals. Is there a way to make it so that the cells with v or s don't affect my other columns?
View 4 Replies View RelatedI would like to use a formula to take the starting date in cell A1 and then have Excel give me a list of all the dates for 2009 excluding Sundays.
So A1 would be 1/01/09
Then Excel would list
Thursday, Jan 01
Friday, Jan 02
Saturday, Jan 03
Monday, Jan 05
Tuesday, Jan 06
etc....
I've tried messing with the WEEKDAY function, but can't seem to figure it out.
I need the VBA/UDF to produce a column of dates between a start date and end date, excluding Sundays.
I can see lots, excluding weekends, but very little for just Sundays.
In worksheet named, " My Overview", if the total sales values are a zero in C47:C59 the consultant name is duplicated in B47:B59 because of the values being a zero in C47:C59. How can i have the formula not duplicate this?
View 3 Replies View RelatedI have a workbook that creates three temporary menu buttons when I open it. These work fine until I try and open more then 1 workbook at a time. This creates duplicate menu buttons (3 times the number of workbooks that I open).
I call up the procedure when each workbook is opened using this code in the "ThisWorkbook" code area: ....