I want to search and match data in Excel,
following are the required and output format.
A
123abc
ujyh
thbd
B
abc
bd
jyh
Want to search column "B" in "A" Output should be in given format.
123abc
abc
ujyh
jyh
thbd
bd
Vlookup doesn't support.
I am having a row with values from 21 to 45 and continued again from 1 to 25
Some unknown number of rows in the beginning
25
26
...
40
41
42
43
44
45
1
2
3
4
.
.
24
25
to find column position of "1" i am using code
Worksheets("sheet1").Rows(2).Find(What:="1", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False).Column
But it is returning column number of 31 and when i delete or hide that column it returns column number of 41.
Cant i search for exact value 1 in my row?
I have sheet 1 that in cells V5:V20 is a data validation drop down box. In cells W5:W20 I have another data validation drop down box. On sheet 2 I have a table that in column A matches the data in the drop down box's in column V on sheet 1. Row 1 on sheet 2 data matches the data in the drop down boxes in sheet 1 cells W5:W20. What I'd like to do is on sheet 1 Cells Y5:Y20 have a VBA code to lookup the data in columns V and W from sheet 2 and return the value.
Windows 7
Excel 2010
I have a file which has in excess of 12,000 rows of data in 5 columns (sample file attached with fake data). The five rows are:
"First Name" "Last Name" "Name" (uses CONCATENATE to combine column A & B) "Email" "Date Attended"
There will be duplications in the list as people will have attended more than once over the years.
What I want to do is search through the email addresses (Column D with D1 being the header) and where there is a duplicate email address copy the cell to the right of the duplicate (F#) into the next available cell to the right of the first occurance and then delete the row with the duplicated email address.
I am on Windows 7 and Excel 2010
I'm trying to search & match data from two different spreadsheets. I will attach my workbook for reference.
The first worksheet is a list of all of my clients I have previously worked with and the second worksheet is a list using a set criteria. The criteria I am using is the UK postal code "AL10".
The clients address (Column B) will be used as a reference to match the address which is located on the AL10 worksheet which is also column B. If there is a direct match then a VLookup function will be performed to display something that can be easily referenced.
The problem I am having is that the address format is different on the clients worksheet then what it is on the AL10 worksheet. I have the feeling I will need to create a search function with multiple arrays but I have limited knowledge of how to do that.
There are some additional notes located in my workbook.
I know that two of the client addresses should match data located on the on AL10 worksheet and the other two shouldn't give a match at all as they don't exist. These are highlighted in yellow.
I have used the Find and replace function to do this but this is rather manual and slow and I would like the search feature to automate this process.
Attachment 308707
I have a worksheet named "List" with static values in columns A & B. I want to search all the other worksheets in the same workbook for the one that matches the value I entered in column B on the "List" sheet. The cell on the other worksheets with the possible match is B5. When it finds a match, I want it to take the data from cell B3 on that worksheet and paste it in Column C of the "List" sheet on the corresponding row (all rows in the "List" sheet have a different value in Column B).
In other words, when "List"b7 = worksheetb5 , then worksheetb3 is pasted to Listc7.
Trying to grasp the concept of using these 3 functions to search for and return values from a data sheet.
The attached spread sheet has performance data for a group of employees.
What I need to do is find a particular employee then return a value for one of the category's.
For instance, I need to find "10TE03 ANGIE HOLLIS" Parts Usage on color or cell C10 in the attached sample.
Sometimes new category's are added to column A adding to the number of rows so a simple offset is not reliable.
Once I get that working, I then need to use a named range to total and average different data points for groups of employees by teams.
Maybe Offset-Index-Match is not even the way to go here?
I need a macro that can search a sheet for a match and then copy all 7 cells to the right of the match. I have attached an example of the sheet that will serve as the database to search, and a userform example that will be similar to the userfrom that will display the copied cells when a match is found. I plan to copy and paste the 7 cells to a different sheet so that the userform can display the results with the control source property. I do not need a way to add to this database. I know very little about searching a database so.
View 6 Replies View RelatedI have two worksheets. Sheet 1 has 2 columns, Column A the restaurant's name and Column B contains the review score. So sheet 1 is kinda like this:
Restaurant |Score
Ruby Tuesdays 80
TGIF 78
Outback 92
Sheet 2, Row 1 column B-E contain restraurant names (only on the top row, like field names).. i.e. I manually put the date in because typically the projected date is different from the actual review date.
-A----------- B ----------------C ------D-------- E-----
Date |Ruby Tuesdays|Olive Garden|TGIF|Ruths Chris|
I need the data from Sheet 1 Column B moved to sheet 2 in the next open row (i currently have data in row 1..the field names and down to row 35). This will be continuous so each time i need it to add the score as a new row in the correct field (restaurant name), IF the restaurant isnt listed, I want a new field named with the restaurant name and then place the score in the correct row and column. So, in the example I'd need Outback added.
I have two separate worksheets:
I'm trying to find a formula that looks at Column A on both sheets (each client is allocated a unique number) and if they match enter in column D of the referral sheet the month they were seen but only if it is a 1st contact (appt type on column D of contact sheet)
Referral
A
B
C
[Code]....
way to do the calculation using Excel 2003
I am using Excel 2010. I am a novice user.
I have a lot of data to filter / sort. I want to initially to create a filter for a column of data - which has the format similar to hierarchical paths to files. The data is a mix of text/numbers. e.g.
pathA/path_X/path_Y/path_Z/lso0_rxs_reg_254__5_0/d
pathA/path_X/path_Y/path_Z/lso0_rxs_reg_253__5_0/d
pathA/path_123/path_456/data_out_reg_17_0/d
pathA/path_123/path_456/data_out_reg_0_0/d
pathA/path_X/path_Y/path_Z/lso0_rxs_reg_255__5_0/d
[Code] .........
Doing an alphabetical sort of this date would return the following order. As you can see while each strings in unique - there are many instances where they are simialr - if you ignore the unique numeric values at the end of the string.
pathA/path_123/path_456/data_out_reg_0_0/d
pathA/path_123/path_456/data_out_reg_17_0/d
pathA/path_123/path_456/data_out_reg_4_0/d
pathA/path_X/path_Y/path_Z/lso0_rxs_reg_230__6_0/d
[Code] ......
So what I want to do is to create a filter for the strings - but ignoring the numeric bits at the end i.e.
reg_[0-9]+_+[0-9]+/d
The strings are obviiously of varying length and the number of hierarchical paths is different, so I can't split string on "/".
Similarly folder paths names can contain "_" so can't split string on this either.
As I don't know how many "/" or "-" instances there will be in the string I don't believe I can use the find function. Also as the amount of number will be different i don't think I can use =right(a1,X) either.
I may be able to search for the pattern above - as this is probabay unique - so maybe it's something like the following pseudo code:
Function GetString(txt As String) As String
With CreateObject("VBScript.RegExp")
.Pattern = "reg_d+(_)+d+//d"
GetString = .execute(txt)(0)
End With
End Function
If I do require VBA code - how do I then use this for creating a column filter? Or will I have to extract the filtered data first from the column (and its associated row data) into another worksheet to use?
Once I have the filter in place I want to create tables using the filtered data - so for example each column value above has a lot of associated data values in each row e.g
26 pathA/path_123/path_456/data_out_reg_0_0/d
32 pathA/path_123/path_456/data_out_reg_17_0/d
8 pathA/path_123/path_456/data_out_reg_4_0/d
So my table would show the name "data_out_reg" and the range of values 8-32
I have a workbook with data on sheet1 and a summary on sheet 2. I want to use two drop down boxes on sheet 2 (B3 and B6) to look up matching data from sheet 2. What formula is best to enable these two look_up values to be used? I saw an example where the two values were separated by a comma within {} i.e. {B2,B6}, but only got an error when I entered the formula into my Excel 2010 sheet.
I have previously used VLOOKUP, but this comes back saying I have to many arguments. I can attach the workbook if that makes it easier to explain.
I work in a small team of 5 people, but each of us is very busy and get emails from our manager asking us to complete different tasks. I have made a userform in excel for our manager to create the tasks in and put all the details in sheet1. This creates each separate task on a separate row. Now I want each of my colleagues to log into the sheet and be able to retrieve a task with the same userform. I am giving each task it's own ID, so I want to be able to search by this and the userform will be populated with the task details. I also have combobox's with the status of each task (Completed, In progress etc.) I want to be able to change these in the userform too.
VB:
Private Sub UserForm_Click()
Private Sub CommandButton1_Click()
Dim LastRow As Object
Set LastRow = Sheet1.Range("b65536").End(xlUp)
[Code].....
Currently using the following code to "pull" a range data from a closed workbook.
[Code] ......
This works well if I know which workbook and sheet I will be getting the info from. What I would like to do is add a function that searches through a few workbooks looking for a worksheet name that matches a value in a cell in my original workbook.
ie. Workbook 1, cell A1 = "Jelly"
search through excel files for the workbook that contains the worksheet named "Jelly" and then copy a range from that worksheet back to workbook 1.
I have created an example of a "Waiting List" for the college, everything I had done seemed to be working before I left for my holiday (apart from the search facility as I was still doing some problem solving, compiling and coding for it).
The Idea is to : Open Excel Document and be greeted by the "Hello" Userform
[Problem #1: This does not automatically show since I have returned from holiday, though there seemed to be no problem with it prior my escape.]Choose from three options;"Save and Exit" saves any changes on/in the database and closes the document"Insert Data" opens "Userform1" to allow users to enter a new row under the exsisting rows of data.
[Problem #2: The data in the sheet was all entered using this feature, but yet again since I have returned from holiday it keeps adding the data only on row 2]
[Problem #3: How to do Auto Formatting i.e. Postcode in Capital Letters]"Search" will open the "Search" userform which I hope to allow users to choose up to three column headings and enter the value to search that column with the parallel textbox value.
[Problem #4: The data which meets the criteria the most is listed into the listbox, if you select a piece of data (double click or "selected") it opens a new userformThe "Edit/Enrol" Userform allows user to Edit, Enrol or archive the row of data.the last Userform I created "Course" is for an admin of the workbook to choose a course and it be auto populated by the information in the worksheet "Code" and create a Mail Merge.
I have an excel sheet with data from Column 'A' to 'S'.
I want to identify duplicate entries in Column 'J' and from those identified columns I want to further search Column 'K', highlight the matching text and then fill text in Column 'L' should have the missing data after matching the 'A' and 'K' cells.excel_duplicate_entries.png
I am using excel excel 2007 in windows 7. I have a search button that works great, however, I want it to also highlight the cell green, when it finds what I am searching for. Here is the code below for the search button I have.
Private Sub CommandButton6_Click()
Dim searchthis As String, Found As Range
Me.Unprotect Password:="123"
searchthis = InputBox("Type Number.", "Property Search")
searchthis = searchthis & "*"
Set Found = Range("A:A,e:e").Find(What:=searchthis, LookIn:=xlFormulas, LookAt:=xlWhole)
If Not Found Is Nothing Then Found.Select
Me.Protect Password:="123"
End Sub
I've a data looks like below..
Name
Yes/No
Marks
Pass/Fail
Susheel
60
Pass
Sanjay
30
Fail
Susheel
Yes
10
Pass
Mohan
21
Pass
I want to populate the data in main sheet for unique name...based on some criteria.
1- Name Should match in both file
2- If "Pass" then it Should be "Yes"
I have been using a macro to search and highlight customer addresses for me, however I have changed my worksheet and now have the addresses in two columns instead of one (D for the numbers, E for street names) for sorting purposes.
What I would like to do is either; find a way to search using data from 2 cells to get a result, or have multiple results highlight and popup.
Using: Excel 2010
Code:
Sub SEARCH_FUNCTION()
Set FoundCell = Sheets("Route").Cells.find(What:=Sheets("Intro").Range("G15"), LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
If Not FoundCell Is Nothing Then
With FoundCell
[Code] ...........
I have two sheets with data. I wont excel to look for a value in colum A in sheet 2 the value to look for is defined in colum a in shet 1. If value is found it should insert in sheet one the value that is in the cell next to the found value. The data is not sorted. I have attempted and failed with using different functions.
View 1 Replies View RelatedIn an Excel sheet; I am trying to match and align data in column C to data in column A, but it is essential that the data contained in the entire row (coln B thru to coln W) moves when Column C is matched and aligned with column A.
Sample workbook attached.
Using Excel 2010.
Rows 1 and 2 (range: A1:CM2) within Spreadsheet X contain the range of data that I need.
Within Spreadsheet Y, Row 6 will change monthly and will be input by the user. The value in Row 6 equals the data in Row 2 in worksheet X.
I need to put a formula in row 4 of spreadsheet Y that returns the value of Row 1 in Spreadsheet X.
Unfortunately, I cannot change the spreadsheet layout, otherwise I could do an HLOOKUP formula using Spreadsheet Y Row 6 and Spreadsheet X Row 2 and be done with it. I assume that I need to use an Index-Match formula, which I am not as familiar with. In addition, all of the online support I have found explains Index-Match with data in columns, not rows.
Please see the attached example. I'm trying to highlight cells only if 2 values in the row match 2 values in another row. If a book title/author combination matches that of another title/author combination in a different row, it would highlight. Some alternate rows will contain different titles by the same author, and some rows will have like titles by different authors...but they should not highlight.
The only situation where highlighting would occur is if the title/author pairing appears in another row.
I'm trying to decide if this would be a conditional formatting/highlighting rule, or if it would be a macro/vbs?
I'm using Excel 2013.
Pairing example.PNG
I have two columns with team abbreviations, one simply says whether they are AL or NL the other is the result of a search with multiple occurrences of the team name. I need to match the long list with the short and put the column header AL or NL in the adjacent cell.
I'm using Excel 2007
The last formula I tried was =INDEX($AY$1,MATCH(BB2,$AY$2:$AY$15,0),0)
The screen shot actually just part of the sheet copied since the screen shot look like a broken html page Ok I give up. I copy and paste a jpg it turns into code, I copy and paste the spreadsheet and formatting vanishes, I don't have a URL for the picture... I did look at FAQs and didn't find picture rules but I will look again. Meanwhile. . .
It's just six columns of data. The short list is in AY from AY2 : AY15
The long list is in BB from BB2:BB505
The column header "AL" is in AY1 and NL is in AZ1
How to match 2 columns in excel and retreive data from the 3rd column ?I have an example here as to what I want to do..
ColA ColB ColC ColD
niki delhi neha
vinay mumbaihardik
kapil bangalorevinay
neha patna
pooja goa
hardik kerala
Here is what I want to do for the above.
ColA and ColB contains a standard information which is supossed to be my reference. ColC contains my queries for which I need information about their place in ColD.
So I need to match ColC with ColA, so as to retrieve the matched data (between ColC and ColA) from ColB to ColD. Following is the way I expect my result to be..
ColA ColB ColC ColD
niki delhi neha patna
vinay mumbaihardik kerala
kapil bangalorevinay mumbai
neha patna
pooja goa
hardik kerala
I received an answer in that link
"=INDEX($B$2:$B$6,MATCH($C2,$A$2:$A$6,0))",
Which when tried, surely worked a few months back. I am now using excel 2010. I tried the same again, but this time it does not work for me. Is there something else to do which has been changed in the new excel 2010 ?
I've already found a TON of threads about this process but nothing that matches specifically what I'm trying to do.
I have a spreadsheet that I'm using to auto fill other tabs with data that only matches specific criteria. Here's what I'm looking to do:
Columns I, J, K, and L may be marked as either Y or N (or blank). I have different sheets that require 1, 2, 3, or 4 of those columns to match Y. For example, on sheet 2 I want to copy the entire row if there's a "Y" match on column I and J. On sheet 3 I want to match "Y" against, I, J, and K. Sheet 4 I need to match only L, etc.
I need the data copied into the existing sheets to start on row 7. I have other data on rows 1-6 that cannot be moved.
I'm running Excel 2007.
I need sheet1 column B to match whatever is next to it in column A to the same value in sheet 2 column A and then return the value next to it in sheet 2 column b and insert it into sheet 1 column B. I have typed in 2 rows manually as example:
View 2 Replies View RelatedI have 3 calculations I would like to make based on data in the spread sheet and I can't seem to get them to work with data from the two separate columns.
I tried a few of the index match max formulas I found here and could only get them to work with one column of data.
I have the spread sheet attached and the 3 calks I want to do are blank on the bottom.
I am using Excel 2011 for Mac
Attached is the example : Example.xlsx
Basically, would like to have the following conditional formatting:
There is a fruit listed and the are person 1 and person 2. If the fruit under Person 1 or Person 2 matches the fruit under Fruit, the cell under either person 1 or person 2 highlights green; if it does not match, it highlights red. If the fruit has not been listed under fruit, such as in cell A3, cells B3 and C3 are not highlighted.
Lets say these are the values in sheet 1, ROW 1:
B1: David
C1: 44673
D1:Mike
E1:88473
F1: Mika
G1 77463
H1:
I1:
J1:
And these are the Lets say these are the values in sheet 2 ROW 155:
B1: 77463
C1: 44673
D1:Mike
E1:
F1:
G1:
H1:88473
I1: David
J1 44673
As you can see, there are the same 9 values in both rows in both sheets ( (no value is important as well).
However, they are not located in the same row number (1 and 155) and the order in which they are written is different.
What I would like to do is to search all the rows in sheet 2,(columns B-J only) and if there is a match for all values in a specific row to a row in sheet 1 (again, order is not important), than the Value in column A from the specific row in sheet 1 will be copied to Column A in sheet 2 for that maching row.