Macro For Matching Up Cells
Mar 14, 2007
I am struggling with a macro that I’m trying to create. Basically what it would do is it would start at cell K1 on a sheet called “Database” and look for the value contained in cell K1 in the entire sheet.
It would paste all matches until no more are found into a sheet called “Matches”. Cell K1 would also be moved to this sheet if matches were found against it. It would then move on to the next non-empty cell in column K.
If no matches were found it would move on to the next non-empty cell.
View 9 Replies
ADVERTISEMENT
May 4, 2009
I m writing a macro that will match duplicating cells in an excel spreadsheet. Here is an example.
BEFORE MACRO
id1 | example 1 | example 2 |
id2 | example 3 | example 4 |
id1 | example 5 | example 6 |
id2 | example 7 | |
AFTER MACRO
id1 | example 1 | example 2 | example 5 | example 6 |
id2 | example 3 | example 4 | example 7 | |
View 3 Replies
View Related
Jan 20, 2013
My task is to combine two large databases into one spreadsheet by extending the number of columns. The data from each database is 90% matching based on an identification number, however occasionally there are additional rows or unmatched identification numbers that need to be kept for analysis.
When this happens, there needs to be a blank row inserted to represent the missing data in the rest of the corresponding row.
I am having trouble finding a quick way to do this because I have approximately 12,000 rows (and columns up to DV when combined).
for example:
p1
data
data
data
data
p2
data
data
data
data
[Code] .....
needs to become:
p1
data
data
data
data
[Code] .....
I am guessing I will need a macro of sorts, So far I have made one column that tell me if the ID's are matching or not (1 or 0) and if they are not matching (0) I manually insert the rest of the row that is missing or make space for the duplicate data (which needs to remain).
View 5 Replies
View Related
May 23, 2014
I am trying to build a staff roster. The staff rotate over a 4 week cycle. the name of the staff member, and their shift needs to be looked up from the key then matched with the particular week. the name and shift then need to populate specific cells.
I have attached the worksheet so you can see what i am trying to achieve.
View 2 Replies
View Related
Jul 1, 2008
I am working on a spreadsheet for a shoe company. I have separate columns for the size, model, color, and item number of a shoe. I get everything except for the item number from a written document; I then have to find the item number for the shoe from another excell document called the Master List.
I was hoping there would be a way to have Excell auto-fill the item number for me. For example, if a shoe is a Red, Athens (the shoe model),size 12, its item number (which can be a pain to find) listed in the row of the Master List is aaabbb. So I want to just enter in the size, color and model number, and have Excell find the item number for me, and fill it in.
I have enclosed an example. Sheet 1 is the sheet I would be working on. Sheet 2 is a portion of the Item master list, which is actually 50k lines.
View 8 Replies
View Related
May 6, 2009
I am new to Excel macros and programming, and am running into a problem that I can't figure out how to solve. I have read through the forum and tried various examples, but I am still not getting the result I am looking for.
I have a spreadsheet that has a variable number of cells in-between blank rows (the data can be 1 to 6 rows). I need to sum up the rows of two columns and see if they equal 0 or if there is a difference. If there is a difference, I want to make the cell red. The exact problem I am having is I can't make it check the # of rows and run the formula based on that number of rows. I am attaching an example of my raw data followed by an example of what I am looking for as the end result.
View 5 Replies
View Related
Apr 11, 2008
im looking for a formula that will find matching dates in column a and then i want to see if column b has a matching time in it
View 11 Replies
View Related
May 19, 2009
I have 2 worksheets - main and match. In the main I have something like the following:.....
There are 200 entries in the main work sheet. In the match work sheet I have something like:......
I need to be able to flag if the entry in the match work sheet is already in the main worksheet by using the unique keyID. This flag will be in the match worksheet.
I am using the following array formula: {=IF(A3='[main.xls]Main File'!$A$8:$A$16,"y","n")} and was hoping that this would give me a Y in my flag column but this only shows a N. If I just do a straight if statement then I get a yes: =IF(A2='[main.xls]Main File'!$A$29,"y","n")
View 4 Replies
View Related
Jun 13, 2009
I have a table which is 8 columns wide by approximately 1000 (+) rows. I am struggling with the following;
if I type in seven numbers somewhere I need to get returns on a full 8 cell match? (and possible partial, but exact matches to 4+out of 8 numbers) All the numbers in individual cells are no larger than 99 and (in theory) are arranged from left to right in ascending order.
Can anyone point me in the right direction?
I don't want to use auto filter as I am just looking for a reference that I am searching the correct number. Does that make sense?
View 10 Replies
View Related
Dec 8, 2011
Is it possible to do the following?
Worksheet1!A2 holds a text value
Worksheet1!B2 holds a text value
Worksheet2!D2 holds a text value
Worksheet2!C2 holds a text value
Is it possible to use vlookup to find a match for Worksheet1!B2 in Worksheet2!C2 where the combination of Worksheet1!A2, Worksheet1!B2 is the same as the combination of Worksheet2!D2, Worksheet2!C2?
View 9 Replies
View Related
Mar 20, 2008
I have a list of unique numbers on sheet 'List'. On another sheet, 'Data', I have a columns of data and column E contains a number which will match a number in the list on sheet 'List'.
What I want to do but don't know how to is loop (i think) through the numbers in 'List' and every row in 'Data' that contains a number is moved so that the data is grouped according to the number. i.e all rows with 78878 will appear beside each other and so on.
Neec code that i could use that will automate this so the user can just press a button and the data is reorganized?
View 9 Replies
View Related
Aug 13, 2006
How do I write a nested COUNTIF formula ( assigning a value of 1 for each cell) for a range of cells that does NOT contain "", "OFF", or "REQ OFF" ?
For example:
A row of 20 cells (A1:A20), 5 are blank (""), 3 contain the text "OFF", 4 contain the text "REQ OFF", and the rest contain anything other than the three values just mentioned.
I need a sum number for all the cells in that row that contain any other value. In this example, the output value should be 8. Out of 20 cells in the row, a total of 12 cells had either "", OFF, REQ OFF, and the remaining 8 had some other value that did not equal those.
Some sort of COUNTIF(AND(... Can't seem to figure this one out.
View 9 Replies
View Related
Aug 1, 2007
I have four columns of info. Two are check #s and amounts from the bank and two are check #s and amounts from a database. How can I delete check #s(along with their amounts) that match? Is there any way to detect check numbers that match but amounts that don't?
View 5 Replies
View Related
Oct 29, 2009
If I have a list (in sheet 1) of names listed in column "a" and numbers listed in column "b" and the same thing (on sheet 2) and I want to run a macro that takes the data from (sheets 1 and 2) and lists the matching names in column "a" (in sheet 3) and the number associated with the name (in sheet 1) in column b and the number from (sheet 2) in column c
View 2 Replies
View Related
Apr 1, 2014
I'm currently having a hard time creating a formula to verify if the contents of 2 cells match.
Example from Spreadsheet -
Column C: MATCH / NOT MATCH
Column D, Row 4: MSG
Column E, Row 4: SMSG
When I attempt to create a formula for Column C, it registers the "MSG" within "SMSG" and lists the result as "MATCH".
View 14 Replies
View Related
Jul 10, 2014
is it possible to provide a formula to match as per the uploaded file. Data in Sheet one columns A and B, compared to what is in Sheet two column A I have a lot of data, and only need a Y for yes, and N for No as a result.
View 3 Replies
View Related
Dec 22, 2013
I want to know if there is any formula to match two numbers which have same digits but are in different style. I have attached a spreadsheet with numbers in column A and Column B. Both columns have the same number just the style is different, is it possible that i get a match based on the digits match and then return the output as correct or wrong?
If all digits match from column A with column B it should show as correct else wrong.
View 2 Replies
View Related
Apr 14, 2009
We have a report with 2 different sheets. On sheet one there are 13,000 rows of data and columns A-H are filled with different data. column A is our item id and column G has a date that states when the item was sold last. Well, the date in column G is wrong for about 7800 rows and needs changed.
The second sheet in the file is the 7800 rows with just the item id in column A and the correct date in column B. We are looking for an easy way to replace the rows in the first sheet with the correct information from the rows in the second sheet without manually replacing it line by line.
I almost forgot to add in, that in both sheets, column A with the item ids are the same item ids on both those sheets. So column A on sheet one has 13000 rows and included in those 13000 are the 7800 item ids on sheet 2.
We tried using the VLookup function that is incorporated into excel but had absolutly no luck, mostly because we have never used this function before. Sheet one already has all the lines of sheet 2, its just that sheet one has the wrong dates for the item ids and sheet 2 has the correct dates. im trying to replace the dates on sheet one from the dates on sheet 2 matching on the item ids from both sheets......
View 2 Replies
View Related
Sep 13, 2013
I have a long master list of registered members, column C has last name, column D has join date.
Now I have a short list of last names with join dates.
I want to compare the short list with the master list to find names that are already there, by comparing the last name and join date.
View 8 Replies
View Related
Jan 8, 2014
I have a sheet containing a list of client name,the date they've been assigned, the type of account they have, the status of the account, and the last update date.
What I'm trying to accomplish is to extract the client name and the type of account on that table that shows as "complete" in status. However, it gets complicated since I need to separate the data that had been 'completed' under the same month, with those completed the other month.
For example, I have been assigned 2 clients this January. I closed one in the same month, and the other closed at February. The first client would be listed under the first table (Completed within the month) and the other would be on the second (Completed on previous months)
Since this would be monthly, I am thinking if it would be more efficient to include the table per sheet (as one sheet corresponds to a month), or consolidate everything to a single sheet... I'm not sure which one would be best.
View 2 Replies
View Related
Oct 26, 2008
I have a sheet that returns 4 numbers where 2 will match. Is there a command that will return that matching value?
For example:
cell 1 = 5
cell 2 = 9
cell 3 = 4
cell 4 = 9
something to return the value 9
View 2 Replies
View Related
Sep 9, 2013
i have a worksheet that is about 100rows x 100columns.
i would like to create some code to find a certain record. for example..
a1 a2 a3 a4
the record might be " tony tony alex tony"
basically, this group of cells could be any where in the group and i need some code to search the worksheet and then outline it. i just want to be able to find a certain block of cells and outline it.
View 2 Replies
View Related
Jul 7, 2009
I have 2 pretty large zip code lists, we will call them columns A and B. I know that I have some from A that match to zips in B, but I have alot more zips in B that don't match to column A. I need to identify all of the zips in B that do not match a zip in column A.
View 9 Replies
View Related
Jun 29, 2006
I have two columns with the same data just totally different orders the third column (associated with the second) has data that I want to sort. I want to keep the order of the first, rearange the second so they match, and have the 3rd column follow the second to the proper location. i need to keep the order of column 1 so i can post into a massive spreadsheet. Theres gotta be a quick formula for this i just have no clue
View 2 Replies
View Related
Oct 27, 2008
I have two spreadsheets, spreadsheet A and B. In each spreadsheet there is a column labeled 'event' and a number in that column. I need to match the event number in spreadsheet A with the same number in spreadsheet B. In spreadsheet B in the same row as the event number is another value that I need copied back to spreadsheet A.
Manually I would have to copy the event number from spreadsheet A and use 'find' in spreadsheet B. Then check in the same row as the event number in spreadsheet B for a specific number, copy that, head back to spreadsheet A and paste it next to the event number.
I've tried to create a macro to do this but when using 'find' to match the event number in spreadsheet B it keeps on using the first event number I used instead of moving down a row onto the next number. I can upload the spreadsheet if needed.
View 2 Replies
View Related
Dec 3, 2007
I've done something similar using functions but I think a macro is best, in essence in calculating in finance world, a Earnings Before Interest and Tax or commonly referred to as EBIT, one often takes out non operating income and non operating expenses.
What I was hoping to do is have a macro
1. Ask the user to shade or highlight which range to calculate from firstly (like Colo's HTML marker with the pointer where you use the border type lines to highlight the boundary first).
2. The macro than inserts a column to the right of the year block highlighted. the macro uses this column to tag the financial line items, ie. in this case below a 1 for income types (can be any tag) and a 2 for expense line items.
3. It than looks through the database of words (and this is where the fuzzy logic comes into it, there are words which would definitely require a tag, but there are others which may require two matching words). I intend to fill this macro with many expressions which auditors and who ever prepare financial reports use as words in their financial statements..
4. So the macro than looks through this database of words for income and also for expenses, and when it finds matches, it tags alongside the finanicial a 1 for income and 2 for expenses .... signifying non operating income or expense.....
View 9 Replies
View Related
Jun 24, 2006
Column A Column B
1 b
1 1
1 2
3 4
I need a macro that if value in column b matches with value in column a, delete it both the value in column b and a and put the deleted value into column c. now my value in my columns is a combination of numbers and letters and it can have this characteristic too: `2076 or `FI7890
View 3 Replies
View Related
Jul 15, 2014
I am trying to copy a row based on the value of a cell.
I have two sheets in my workbook and on sheet 1, I have a part number and a description. On sheet 2, I have part numbers again, but this time I the description is broken up into the format I need.
What I am trying to do is have excel search on sheet 2 for the part numbers, then copy the information that corresponds to the part number into the correct column.
I have tried using Vlookup. But if the part number in row 2 on sheet 1 match the one in row 8 on sheet 2, this will copy over the data from row 2 whereas I need row 8.
If this would be more doable using VBA, that is fine by me. I haven't been able to figure out anything in VBA or in excel formulas up to this point.
View 7 Replies
View Related
May 8, 2014
As shown in the attachment, I'm trying to return value when matching cell B7 + D5 with cell G5:I7. Is there any way to do so?
Attached File: File Type: xlsx Book1.xlsx‎
View 3 Replies
View Related
Apr 19, 2010
I have a column with 10,000+ lines, and lots of them are duplicated.
I have a formula that tells me how many times they are duplicated, but it only looks for 100% matching cells.
Now, lots of cells are partially duplicated, for example:
A1 - Team Abcde
A2 - Team Abcde Fghi
B1 - Team Abc
I would like to have a formula giving me a percentage of how two cells are similar to each others by checking the words. In this case, B1 is 50% similar to A1 because only the word 'Team' is duplicated. B1 is only 33,34% similar to A2.
View 14 Replies
View Related
