1. Write a sub that captures the existing lists in two arrays and then creates three new arrays of::
a. Customers who purchased only last year,
b. Customers who purchased only this year, and
c. Customers who purchased in both years.
2. After these new arrays have been filled, write their contents in columns D, E and F of the worksheet Auto Merged Post Until 24 Hrs Passes;I do not know how to do part C.
I have a tab with different city names and then a a series of answers to a number of different questions, what I need to do is create a macro which looks a table on a different tab, and if column b matches any of the words in the table to then print that entire row of answers.
So if the information found in R1!M3:M21 is found in Results!B:B then print that row to sheet R1. If the information found in R2!M3:M22 is found in Results!B:B then print to sheet R2 etc.
This is the only other way I could describe it, however I don't think it can be done as a formula anyway (even though it is somewhat gibberish)
IF(ISNUMBER(SEARCH(R1!M3:M21,Results!B:B))print the row,move onto next row)
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 have a workbook that has worksheeets for every day of the month. The data in the worksheet consists of columns (employee #, name, clock in/out times, and break penalty).
What I am trying to do is create another worksheet that searches all the other worksheets for a "yes" in the "break penalty" column and then create a list of all the employees that received a break penalty for the entire month. I would like this to be able to auto populate throughout the month as data is entered and not have to use a filter every time I want to compile this list.
I have a list of names in B8:B200 (unique)
I have another list of names in I7:I15
I want to count how many names in B8:B200 that matches any of the names listed in I7:I15.
I have two spreadsheets that use a Number as a Key. I need to compare the numbers on list 1 to the numbers on list 2 and add any values that exist on list 1 but not on list 2 to the end of list 2. List 1 is in Column B, List 2 in in column C of a different sheet in the same workbook.
View 2 Replies View RelatedI have a problem with finding the value in column B that is found within a range eg: <=1 & >=5 and return the corresponding values found in column A. kindly refer to the attached sample for reference.
View 2 Replies View RelatedI am trying to list the row addresses for found cells, however I am not sure how to achieve it, below is my script.
Code:
Option Compare Text
Sub TestN()
Dim rng1 As Range
Dim s1 As String
Set rng1 = Range("A1:A100")
For Each cell In rng1
If cell.Value = "Help" Then
[code]....
I am working for a non profit humanitarian org and I am trying to modify an excel 2003 spreadsheet to change the formatting (color) in one cell based on the value of another cell. Obviously this is easy with conditional formatting, except that I have a list of around 25 unique values and formats.
Could anyone point me in the direction of some code that can do this? I am a relative novice to Excel when it comes to VBA and scripting things.
I have two worksheets. Worksheet One and Worksheet Two. In Worksheet One Column A is a list of numbers. I want to look for all of those numbers in Worksheet 2 Column A. Any of the numbers in Worksheet One Column A that are not found in Workseeht 2 Column A I want to list in Worksheet One Column B. I do not want to compare cell to cell but if any number in the first column is found in any position in the second column.
View 3 Replies View RelatedI have a list of email addresses in column A of a sheet1, and a list of "Do Not Send" email addresses in column A of sheet2. I'd like to have a macro that would delete any cells in column A sheet1 that were listed anywhere in column A of sheet2.
View 5 Replies View RelatedI am trying to look in B1:B100 for the Cell that matches A1 and C1:C100 for the cell that matches A2 then return the value from D1:D100 that meets the 2 criteria.
View 6 Replies View RelatedYou can find attached the workbook I am working on.
In my workbook I have four sheets. Only two sheets are relevant in this case: "Sheet1" and "List". On "Sheet1" there in column A there is big range of codenumbers (highlighted with red).
On the "List" worksheet I have a smaller list (highlighted with green).
I have a code also in module1 but the code is not working.
I would like my macro to do the following. Check the code number from the "List" worksheet (green) and search for it in "Sheet1" column A (red). If this codnumber can be found in column A then leave the number on the green list. If it cannot be found in column A then, delete it from the green list. If you open the file, you can see on the "List" worksheet that there are three code numbers in the green range. The upper and the bottom code number can be found on the "Sheet1" column A (red) range, but the middle number cannot be found. So if the macro would work correctly then it would delete the middle number, and leave the other two untouched!
I have long text values that include names in cells A1:A100.
In cell D1:D10, I have a list of names that I would like to check if any of them is found in each of the cells in A1:A100 and if yes, return TRUE in column B.
So in a way, what I need is a a bit like the SEARCH function, only that I need to find multiple FIND_TEXT values. If just one of the names in D1:D10 is found, the formula should return TRUE.
I have two lists, one has 250 items, the other 4500, both contain a String
I need to compare any word within a cell in the short list, against any word within a cell in the long list.
The returned value needs to display both the short list item, as well as the long list item for manual comparison by an analyst.
I have two columns of account numbers. Column A would be considered the master list. Column B is data that changes on a monthly basis. What I would like is for a macro to compare column B to Column A and if it finds any account numbers not listed in column A it will insert a row into column A and copy/paste the account number from column B to A. Both rows are sorted assending so if its possilbe I would like the macro to insert the account number where it belongs or maybe just resort column A once it has finished. My knowledge of macros/VBA is very limited at this point
View 2 Replies View RelatedI would like (in excel 10) to highlight cells where there is NO DUPLICATE found In Columns "E" on Worksheets 1 & 2. Is that possible ?
View 7 Replies View RelatedI have a spreadsheet of 12 columns (A to L), with a list of student names under each down to row 31. Each column represents an after-school club that the child can attend.
However, a child can sign up to multiple clubs, so their name can appear in several places across the spreadsheet.
What I would like to do is have a 13th column which is an overall list of students who have signed up to at least 1 club, i.e. their name appears at least once in columns A to L.
How do I achieve this so that only unique entries are listed and duplicates are removed in this 13th column?
I've been trying to create a formula that will take data with a specific status and put those in a list on a summary worksheet.
On my data worksheet I have two columns a category/ name column and a risk/status column, on my summary page I want to generate a list solely made up of names that have a specific status.
I've attached a sample.
i have list in A2:A4 (description) and B2:B4 is the Group of.
now i want B2:B4 fill using E2:G2 (group list which is Animal, transportation and fruit) if one of the key word in E3:G5 found in A2:A4
A2: people like to eat apple
A3: car is very expensive
A4 : dog is human best friend
E2: Animal, F2 : transportation, G2: Fruit
E3:E5 = dog, cat, horse
F3:F5 = train, ship, car
G3:G5 = apple, banana, watermelon
result i want is :
B2 :Fruit,
B3 : transportation
and B4 is animal
I'd like to have a list printed of all the "qualifying people" found in a range, and then sort their corresponding price values from highest to lowest in Columns A and B. EXAMPLE:
RANGE: D3:D20 - Numerical
RANGE: E3:E20 - Text (names)
RANGE: F3:F20 - $$$
I'd like to search column D for any values of 2 or higher. When it finds a 2 or higher, I want it to find the corresponding name in the SAME ROW in column E, and of course the corresponding price in the SAME ROW in column F. Then I would like only those qualifying people "with value of 2 or higher" to be listed in order from highest price to lowest price in Column A, and B.
COLUMNS
D--------E-----------F---
0-----Mike Bob-----$52.65
1-----Dave Jon-----$42.50
2-----Jane Doe-----$37.65
0-----Gary Lon-----$25.50
0-----Joey Saw----$35.65
2-----Mike Jon-----$35.65
1-----Kate Low-----$38.68
2-----John Doe-----$40.00................
I have a range called List that has 3 columns of data:
Corporate Human Resources Services PMA
Corporate Procurement Director
Ops Central Customer Services Lost Property Office
Ops Travel Information Travel Info Call Ctr
Ops Travel Information Centres Travel Info Centres
I have 3 columns on the entry sheet called Business Unit (Col A), Department(Col B) and Sub-Department (Col C). I want to limit the choice in column B to based on the selection in Column A in the form of a drop-down list e.g choosing Corporate gives a list of Human Resources Services; Procurement.
I am using the list option for field validation and the following formula in cell B2: =INDIRECT(VLOOKUP(A2,List,2,0)). I'm not sure what I'm doing wrong as I don't get a list of options just a blank cell. Do I need to use a different formula other than VLOOKUP..?
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 am trying to compare multiple column in a worksheet to find
common component in all the columns and what is unique to a particular
column only. And list the results/finding in adj column. What i am
trying to accomplish is something as below.
BEFORE
Sheet1 Sheet2 Sheet3
Column2 Column2 Column2
02-1234-12 07-1234-12 02-1234-12
04-1234-12 03-1234-12 02-1234-12
05-1234-12 02-1234-12 06-1234-34
AFTER
Common to all Unique to sheet1 Unique to Sheet2
02-1234-12 05-1234-12 07-1234-12
I have this nice formula (listed below) that I'm trying to use to get the employee names that belong to each manager. How ever using the first formula I only get the first name in each cell and using the second formula (associates is a define name for the range A1:A70) gives me name that do not belong the that manager....
I would like this formula to only pull names from the manager that is listed in the C column....
I have built a macro, but want the sheet to select all the rows until you meet a empty row then stop pooling etc..
See below, i am just selecting the rows when I recorded the macro. I want to tell the Macro, pool all the rows starting from row 7 going down until you meet a empty row.
What should I do to tell the macro stop pooling once you meet a empty row?
Sub Macro2()
'
' Macro2 Macro
' Macro recorded 11/17/2008 by DHL User
'
'
Sheets("Air Freight Rates").Select
ActiveWindow.ScrollRow = 952
' Removed additional scrolling
Rows("7:2001").Select
I have a need to combine multiple columns into a long list of results in 2 final columns. Here is my example...
Current layout with varying number of data points per row...
ColumnA | ColumnB | ColumnC | ColumnD | etc...
example1 | Test1 | Test2 | Test3 | Test4
example2 | Option1 | Option2
example3 | Number1 | Number2 | Number3 | Number4 | Number5
Desired Result
ColumnA | ColumnB
example1 | Test1
example1 | Test2
example1 | Test3
example1 | Test4
example2 | Option1
example2 | Option2
example3 | Number1
example3 | Number2
and so on and on and on until all rows have been filled in down the file...
Is this possible?
I have a userform where I can select multiple items in a listbox and add them to another. I also have the ability to filter the first listbox to make finding items easier. The issue I am having concerns the clear filter button. As currently designed, the clear filter button will reset the initial listbox back to its default values. Ideally, I would like it to reset to the default values excluding those values that currently in the second listbox.
The entire code is below for reference, but it's the sub ClearFilter_Click that I am struggling with.
[Code] ....
Fill with (blank) when no data found
I have the following data lets say:
im having when it comes to creating a drop box. When I click on a cell or several cells and go to click data validation, I get "No cells were found"
View 2 Replies View Related
