I have posted in a existing thread at mrexcel as i believe my problem and possible solution is very similar to that already detailed there. However i have not recieved a response there yet and was looking to draw upon more minds here ...
View 9 RepliesIn my spreadsheet, on the first worksheet called "Working". Column A, called "Results", contains carrot ^ delineated string values in each cell (i.e."john^apple^pear^banana^grape^love^heart^pickle"). The majority of string values in every cell in my "Results" column match a "source" column of Pick-List Values, called "Fruits" in the same spreadsheet, however found in a different worksheet called "Lists" (also in Column A). I want to perform 3 functions against my "Results" Column on worksheet "Working":
1.) Report In Column B: Analyze column "Results" by cell and return carrot delineated string values for only those that match my "Fruits" Pick-List
2.) Report In Column C: Analyze column "Results" by cell and return carrot delineated string values for only those do not match my "Fruits" Pick-List
3.) Report In Column D: Identify and return all unique values in "Column C" as a List.
Attached is a simple example of what I'm trying to accomplish called " Sorting.xls"
I have a column O with the heading "Return Visit Required". Each cell, starting in row 5, has data regarding a job. So each row represents a different job.
If the cell in a certain row in column O has a value="Yes" then I need 1 row added below that cell. If the value="No" or "" then do nothing.
I need the value "Yes" to be non- case sensitive.
I am trying to write/ find a macro that will open a new worksheet when Y is entered into a specific blank cell;
View 7 Replies View Relatedwhat I need is to insert the data in a cell, and instead of copying this same data to other cells in the same sheet or in other sheets, I need a way so that once I insert the data in one cell, it will be copied to the other cells at once.
For example, if I insert data in the cell [Sheet1,A1], it will be copied to [Sheet2,A4] and [Sheet3,E8] and [Sheet3,I7]
I am working a project where I need to copy/insert a Row of data onto Sheet 3 if values from Sheet2 are located on Sheet1. Here is my code so far:
Option Explicit
Sub move_rows()
Dim ws As Worksheet
Dim ws1 As Worksheet
[Code].....
My cell contents looks like this.
H1111111111111
I want it to look like this..
H11-111-111111-11
I know this can be done by formula but i dont know it. This info is in col B.
I have two spreadsheets in Excel 2003.
Spreadsheet 1 has 10 columns of data (A-J). I want to copy a variable number of rows from spreadsheet 1 to spreadsheet 2.
When I paste into spreadsheet 2, I'd like to automatically insert blank cells in three places, taking the total number of columns to 13. I'd like columns C, F and I to be blank, and the last column with data to be M.
I will perform this task regularly, and add the copied cells to the bottom of spreadsheet 2, so I'd only like to insert blank cells within the range that I'm copying, not the entire spreadsheet.
I will then populate the blank cells with a VLOOKUP function. Do I need another macro to automatically add the formula to the cells, or is there a way to include this in the cell-inserting macro?
I have several worksheets (Labeled Sheet1,Sheet2,sheet3) What I need to do is to step through each row in sheet3, and do a search in sheet1, it the data was found, then return the row number. I then will need to copy data from sheet1 (rowfound columnA , through rowfound CoulmnBd to sheet3 current working row columnK
View 3 Replies View RelatedExample:
On sheet "Inventory List" is the value there is a value in cell A2. If that value is found in sheet "FHS Locker List" I would like the box highlighted. (I can do the highlighting) I would like to copy the formula down for cells A2-A900. It would be looking in cells K3-K900 in the "FHS Locker List" sheet.
Worksheet cells A1 – A25 have sequential numbers 1 – 24
the following elegant code generates every permutation of six numbers from the list into Cols C – H
VB:
Option Explicit
Sub Combinations()
Dim rRng As Range, p
[Code].....
I have a spreadsheet which has a source worksheet, containing 2 columns with thousands of client numbers and account numbers. I have a destination worksheet which contains certain account numbers which are featured in the source sheet.
What I need to do is pull the matching client numbers across from the source sheet into the destination sheet. An example is attached.
I've just started with VBA and are trying to figure out following:
I'm using a validation list where the user choose one of twelve alternatives. The option she or he made is found in one or more rows in column B. If there is a match between the alternative in the validation list and in column B I want to copy some of the cells in the same row as the match in column B (to be exact, I want to copy the cells in column E, J, N and P) to another sheet.
I've succeeded doing this with one row but I don't know how to do without using that same code over and over again until Excel has made it trough all the rows. And there is over 200 of them.
I'm attempting to highlight or remove cells if the 3 cells contained in column A, B, C match three cells in D, E, F.
SKU1
Image1.jpg
Image 1
SKU1
[Code].....
So for the cells contained in A, B, C row 2 to get highlighted/removed, they have to be a perfect match to the corresponding cells in D, E, F row 1.
K so basically i have a cell where based on the above formulas it displays a number.
What i want it to do is only display a number if it matches one of the numbers in the 9 cells above it. so for example
cell1:2
cell2:4
cell3:6
cell4:8
cell5:1
cell6:9
cell7:89
cell8:0
cell9: 11
and i want the number to display only if it is one of the numbers shwon above.
I am working on large sets of data (more than 50,000 rows of data). I have two sets of data. Set 1 and Set 2 (master data) on the same worksheet. Both the sets of data have three columns each. I am using EXCEL 2007. I was able to accomplish step 1 below.. but I am totally lost with step 2 since i have an additional criteria for the "year".
I have attached the excel sheet as well. This is what I am trying to accomplish:
1) I want to find exact matches in set 1 and set 2 and highlight it or do something to show that a match was found. The challenge is the data in set 1 can occur anywhere in set 2.
2) Add to the complication .. my criteria for matching the year is different. If the Set 1 "year" is equal to or greater by 1 yr or greater by 2 yr when compared to Set 2 "year", I want to treat it as a "match".
For example, from the data attached:
Set 1 data in row 4 is: ATLANTIC ABSECON 2004
Set 2 data in row 3 is: ATLANTIC ABSECON 2003
I want to treat these two data as "MATCH" since ATLANTIC matches ATLANTIC, ABSECON matches ABSECON and according to one of my criteria for year, Set 1 "year" is greater by 1 yr than the Set 2 "year".
I have a page of data that i need to summarise/calculate, i thought sumif would be the correct formulae but i can't get it to work...
Sheet 1 - Data Recomds Emp Name, Weeks 1-52 showing no of hours to adj
Name
WK1
WK2
Wk3
WK4
WK5
WK6
Oliver
-1.5
[Code] .....
Sheet 2 - Summary by month - to Calculate the no of hours for the period per employee
Name
Month 1
Avery
Require Sum for employee Avery Wk 1-4
[Code] ..........
How to check if a range of cells matches specific text.
Example:
A1 - dog
B1 - cat
C1 - mouse
I want the formula to give me "yes" if dog is present.
=IF(A1="dog", "yes", "no") is for one cell. However if I do it this way:
=IF(A1:C1="dog", "yes", "no") I get an error.
I have two columns, some of the cells in these columns contain more than one value separated by a space. Example:
Col A Col B
1.99 1.69
39.95 6 119.94 29.99 6 149.94
135 250 135.00 250.00
11.6 11.60
What I am trying to achieve is to test that the values in column A match those in column B and have a TRUE/FALSE response in column C. At present i'm just using =a=b, however I want the formula to give me a TRUE response for the third and fourth lines in my example, as I want it to ignore the zero after the decimal place, at present it gives me FALSE.
So the results I want to see in col C for the above example are:
FALSE
FALSE
TRUE
TRUE
I can solve my problem with a pivot table, and with VBA easily...however, I'm interested in knowing if this can be done with formulas (array formulas using index/match I'm assuming).
Goal: On sheet1 I have one column with products, then the column next to it will have an "Y" in it if the product is to be selected (blank if not). On sheet2 I want to create a list of the products that were selected (having the "Y"). The only thing stumping me is that I do not want spaces between the product list on sheet2...just a nice continuous list. Example:
Sheet1
cup Y
bowl Y
spoon
fork
knife Y
Sheet2
cup
bowl
knife
..not..
cup
bowl
knife
I need a formula that will do this:
for all rows where data in column A matches, does data in column c & f also match? If it does, then put a 1 in column n; if it doesn't, put a 0 in column n
In the attached excel sheet see the sample example tables 1.1.1 and 1.1.2. I have to use it for official purpose and my manger has given this task to complete it. I cannot even compare the forumlas and make it same for all the tables available.
How can I attach the excel file...
I have a reference vertical table which can be up to 30 long (I just put 5 here to keep it simple):
Sheet 1
A
B
1
[Code].....
And I want to return the data in the B column depending on whether the data matches either of the words in the corresponding A column.
E.g. Sheet 2
C
D
10
laki
The formula in this cell would return B1 because C10 = A1
11
katla
The formula in this cell would return B4 because C11 = A4
I need to line XYZ to XYZ as well as the information attached to XY&Z
i dont really know how else to explain it but i was hoping there was a macro or something out there that i could use to do this would make it a lot easier
I have a macro that I am working on which I have gotten stuck on. It compares Column A and Column C and any numbers which match are pasted into Column D.
What I am getting stuck with is I need to copy and paste the data for column B to Column E as well. So in other words.
If column A and B match then the number from Column A that matches B will be pasted in column D along with the value that is right next to it in Column B.
Attached File : MatchingMacro.xlsm
have two worksheets. sheet1 has order information on it with orders, dates, customer names. sheet2 has customer name list. How can I (via vba) search through the order sheet and find the most recent order date for each customer in the customer name list. post that most recent date next to the customer name on sheet2.
View 4 Replies View RelatedLooking for an excel worksheet to keep a record of |MOT's due
By entering the Name, Reg, Date, Comments, Reminder
by way of a userform
after the data is entered it is stored on anther worksheet..
every morning when they get to work. they want to open it and on the MotDataEntry sheet it needs to bring up the Mot's that are due in a months time (reminder) ,name reg etc
I have a spreadsheet with 3 columns: Name, Number and percentage. I need a formula to return the name that has the percentage and the 2nd highest percentage. the spreadsheet has 5 rows, not including the columns labels.
View 2 Replies View RelatedI have data exported as .txt from an external program in the following format:
Sample Name Marker Allele1 Allele 2 ....Allele n
AAA D8S1358 10 11 XX
AAA D16S539 16 19 XX
and so on.
Each "Sample Name" will have 16 different "Marker"s and upto 10 different "Allele" at each "Marker". There could be upwords of 200 Sample Names on each exported sheet. Is there a simple way to compare all of the data from each "Sample Name" to each other and possibly to another sample set that will always be static but of the sample general makup as this?
I need to create a formula that will give me True or False if text in a cell matches any cells in a range.
For example:
Column A and B have text:
Account
Date
Name
Species
Column C has other and sometimes matching text:
Name
Deal
What formula would I use to find out if all cells in Columns A and B are represented in Column C?