In Col A, from A1:A12 I have
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
In column G from A1:A6, I have
6
9
7
11
15
How could I return an 'x' in column C against the A values corresponding with the G values, for example an 'x' in C6, an 'x' in C9, an 'x' in c15' etc? And a blank for example at C8 and C14.
getting correct formula and normally there are people much smarter then I in the
discussion group. What I have is a workbook with 4 worksheets and on on of the worksheets I have a layout like this. (sheet 1)
A B C
1 Name Origin Driver #
2 Mrs Soandso 125 anywhere
3 Mr. Someone 327 anywhere
Drver numbers are blank
On another worksheet(sheet 3) I have a list of subscriptions (I.e drivers
numbers already filled in) I have this set up as a list.
A B C
1 Name Origin Driver #
2 Mrs Soandso 125 anywhere 37
3 Mr. Someone 327 anywhere 39
What I am looking for is a way to pull the drivers numbers from sheet3 into sheet1. so basically in sheet 1 if a1 and b1 match the name + origin in sheet 3 it will pull the driver number from that row into c1 sheet1 . If nothing matches then it should leave it blank. The data in sheet 1 changes everyday for me as I get a manifest on who to deliver wheels on wheels to, my problem is while I know all the correct drivers go to what people, I can never have a day off as someone filling in for me has to go through 400 addresses and look each one up and put coresponding drivers number into it.
I think I've been at the Xmas sherry for far too long as I can't see the wood for the trees with the following code. It is supposed to find the position of the string_to_find within range_to_match.
I have checked and the address returned locally by range_to_match.address is correct. However the individual items (current_item) are coming back as blanks (as in the Msgbox) - rather than the members of the range.
I did try and do this via application.match but again got an error.
There is an option explicit at the very top of the module.
Code:
Public Function match_string(range_to_match As Range, string_to_find As String) As Long
Dim i As Long
Dim pos As Variant
[Code]....
I have two majors (classes) listed on two differents sheets and need to put the matching classes on a third sheet.
View 3 Replies View RelatedI am trying to have an order form auto populate information, however I have run into a small problem when doing so. I am unable to use VBA because it is locked by whoever originally created the spreadsheet so I need a formula solution if possible. I would like for the worksheet "Order Form" to auto populate the type of printer cartridge when there is a 0 in the column "On Hand" on the "List" worksheet. I know a loop could do the trick but I was wondering if maybe a vlookup or if statements could get the job done. Here is a completed sample of what I would like to happen.
View 4 Replies View RelatedI'm working on a travel form, which allows people to enter a travel itinerary. From that, I want to be able to extract the cities where they are spending one or more nights - so that I can then do the calculations for accommodation allowances.
I am able to calculate the number of nights stay in each city where there is an overnight stop. But I'm stuck on how to extract every combination of City and Nights where Nights is greater than zero - there is no need to calculate for cities with no overnight stay.
I have attached an example spreadsheet.
Some notes:
- the itinerary and accommodation tables have to remain separate, as they hold more data than in the example,
- the itinerary table can't be sorted for number of nights. It has to be in chronological order for each segment!
Every lookup function I check out seems to demand a sorted table.
I have code that runs through multiple arrays trying to match data to items in the arrays and it takes a long time to run.
Code:
dim a as long
dim b as long
dim c as long
dim d as long
dim e as long
dim MyAarray as variant
[Code] ..........
That's basically what the code does. however, it takes an extremely long time to get through with everything as each array increases in size.
I have two worksheets: A and B.
Worksheet A:Contains 2 columns: Issue# (Col. A) and Program (Col. B).
Issue# contains a list of multiple issues. There are several instances in which the issue# is repeated.
Any particular Issue# field may have several issues in it, delimited by a comma.
Program is a program associated with the issue and this column also contains duplicates.
Worksheet B:Contains 1 column: Issue# (Col. A)
This is a unique list of issues#'s.
All Im looking to do is parse all Issue#'s from Worksheet B and have some way of knowing if that issue# is anywhere in Worksheet A. Most importantly, I need the "indicator" to be displayed on Worksheet A. This way I can see what program(s) is/are associated with the matching issues.
A couple other notes:All Issue#'s in Worksheet B are referenced at least once in the Worksheet A Issue# list
There are several issues in Worksheet A which are not referenced in the issue list on worksheet B (of which I dont care about)
I really hope that makes sense, but if not...
Here's the best example I could come up with: ....
I have Workbook A populated with skus from a report and I want to remove all rows in this workbook that do not have a matching sku in Workbook B. Each workbook contains only one sheet and the skus are in column 1 of both workbooks.
Also, I am looking for a good resource/tutorial for working with workbooks, worksheets and ranges within. I don't anticipate this being the last time I will be confronted with this and would like to learn more.
I am trying to compare two colums. They both contain numbers mixed with letters. I am wanting to match only the numbers in both not the letters. Example:
column a = m454 column b = fsh454-1
m543 fst998-2
m998 fsm434-1
my match is m454 and fsh454-1, m998 and fst998-2. The items can be in any order in the column. The end result I want to indicate the match by putting an X by column a item that matches column b.
i need to match 2 columns on one worksheet with 2 columns on another.
One column is alphanumerical (Reference Numbers), the other is company names.
I need to ensure that both the Reference and Company name match from one sheet, with the company and reference from another.
e.g if A1 and B1 on Wks 1 = A1 and B1 on Wks 2 = TRUE, anything else is FALSE.
There is likely to be Reference number and Comapny name duplicates, therefore the trick is to ensure that the number of duplicates match?(I.E ABC Company, Reference number 1234 may appear 5 times on worksheet 1, however if it is only on Worksheet 2 4 times, then this must be flagged).
1) How do I get the three (3) items columns to be right underneath the ''Recovery Turnaround Days of Files Per Cheque'' heading
2) How do I get each of items #1 - 3 column, to have each their own color (that is not the same color as the '' Average Recovery Days '' column color) ie;
Item #1, 30 = I would like it to be say, Red
Item #2, 35 = I would like it to be say, Pink
Item #3, 30 = I would like it to be say, Yellow
3) How do I get the column under the ''Average Recovery Days'' heading to have it's own color scheme as well, such as as say the color ''Purple'' (or any other color that is different from all the other 3 columns).
how you go about putting the columns so they align right underneath the particular heading, that would be awesome. Also, how to have a color scheme for the ''Average Recovery Days'' to have it's own color (For the column under this heading to have it's own color, what ever color that is), that does not match any of the column colors in items #1 - 3 (under the heading ''Recovery Turnaround Days of Files Per Cheque''),?
I want to add these numbered steps onto the macro I already have, which I pasted below.
1. Starting in Row 2 in the worksheet named Report 1 look at contents in Column A then look at the contents in Column C, if the contents in Column C do not match exactly the contents in Column A then copy the contents in the range column C:E from that row till the last row in Column C:E.
2. Now we will have a range that is made up of cells from C:E. I would like to move that range down a row at a time, until the first cell in Column C from the range Column C:E matches exactly the contents in Column A. Then I would like to continue this pattern for every row in the worksheet.
I have two spreadsheets, both contain matchable data. First one with amounts. Next one hs customer name, period and amount columns.
View 3 Replies View RelatedI need to match the first column with the second column, and if is the same value, copy the value in third column to the forth column.
View 1 Replies View RelatedI have this work sheet with several formulas in columns Z to AD. All of them highlighted red work fine as for as I can tell. I am stumped with the one needed for the cell highlighted yellow AD2. It should count all the dates in AD1 that are Requested Changes Made and/or Rejected in Column "M". AD2 is a total of today minus 8. Equipment Change out - TEST.xlsx
View 5 Replies View RelatedI have a list of line items and I want to be able to see how many duplicates in Column A and B.
Column A has text field and column B is a numeric field
I thought I could use INDEX MATCH but I'm unsure.
I have two columns of data each with repeats. I want to be able to look up the third column of data and then list the multiple results attaching to the first two columns.
I have attached a dummy spreadsheet of what I am trying to achieve.
I have two worksheets, sheet 1 and sheet 2. On sheet 1, I have a information on two columns (Column A and B) Column a has information up to row 10 (aaa, bbb, ccc, ddd, ...). On column B, I have 111, 222, 333 . I have same information on Sheet 2. However, sheet two has an additional column (Column C) with 10 rows on information z, y, x, w, v ...
What I need to do is to match column A and B in sheet 1 to column A and B in sheet 2 and if both columns matches on the same row, copy the value in sheet 2, column C and paste it on sheet 1 column C.
Important: Information on sheet 2, column B may be twisted (As they may not appear exactly the same as in sheet 1. Which means 111,222,333 can be 222,333,111 along the column). Therefore, need to match the entire range.
I need creating a formula that matches the values between two separate columns with results from that matching in the third and fourth columns.
Example: Column A contains the first set of values and column B contains the second set of values. The result in column C would be all the values that are in both columns A and B and the result in column D are all the values that are not found in both columns A and B.
Column AColumn BColumn CColumn D
122123123122
123133322133
231221323221
311322231
322323311
323333333
See attached spreadsheet : match_values.xlsx
Alright, so I need data in column A(Last names) and data in column B(First name) to match their respective names on another sheet. When a match is made, it will return column C(Hours worked).
I originally used Vlookup to match last names, something akin to =VLOOKUP(A:A,Week1!A:P,4,FALSE)
But this does not account for employees that may have the same last names. Is there a way to combine two vlookup's? Or is Index a possible solution?
I have two columns listing about 400 and 500 names respectively.
First Column has names in alphabetical order and the second column is random.
One column has names in this format: Last name, First name and the other column lists the names as first name and then last name without the comma .
I am trying to see if the list of names in one column exists in the other.
Is there a way to see this without changing the format of the names in each column? If so do I have to erase the comma?
Name Lists (1).xlsx
I am trying to 'stagger sort' multiple columns, but am having some difficulties.Ex.
Say A and B are lists of part no and C is data corresponding to column B. I have:
A B C
1 2 .
2 3 ,
4 4 ;
5 7 '
8 9 "
I want:
A B C
1 _ _
2 2 .
_ 3 ,
4 4 ;
5 _ _
_ 7 '
8 _ _
_ 9 "
(the _ are just supposed to be empty place holders, without them I have formatting issues)
Is there a 'quick' way to sort them this way?I am attaching the actual file that I am working with.
My requirement is to match 2 columns of two different sheets. I know about Vlookup for exact matching but here I neeed to do partial matching.
For Example: Sheet1 Company Name= Opera Technologies
Sheet1 Address= 104/Main Street
Sheet2 Company Name= Opera
Output should be like this in different column: Opera, 104/Main Street
I have 2 columns of data. Both should ultimately have identical data,
but both have extranious data I need to separate. These lists will be
300+ long, so by hand is taking forever. Example:
Column A Column B
12345 12344
12346 12345
12347 12346
12348 12347
12349 12349
I need a way to extract the 12344 from column B since it is not in A,
and 12348 from A since it is not in B. They do not have the same number
of entries.
I have a problem in my list cheking. I have two sheets in my attached excel file. I have mordified for my own job security purpose. I need to check the individual cells of "parent" column in "Missing Asset" with "parent" column of "Asset" worksheet. It means, presence of cell text of A2 of "Missing Asst" sheet has to be checked with "A" Column of "Asset" sheet. If A2 text is present in "A" Column of "asset" sheet then "Y" should appear in B2 cell of "Missing Asset" sheet. Else "N". Its just Column text checking wothin two work sheet & then identify the cell which is not match. I guess "VLOOK" can work. Is it not?
View 3 Replies View Relatedi am using this spreadsheet to monitor when equipment is being borrowed in the office and want a quick view reference (columns A-C) based on the data entry in columns &G. Please note that htis is an example as the original file is confidential, if someone can answer my query here i will transfer the formula to my actual workbook. In the actual workbook the data entry and quick view will be on different sheets if this makes a difference.
View 3 Replies View RelatedI have the following columns (O & S):
S O
-----
1 3
3 7
6 6
8 4
1 3
2 8
1 6
I want a code (Formula) do the follwoing: Matching number 1 in column S with number 3 in Column O and count how many matches found in the two columns, Which in this case only two.
I have a document where there are hundreds of rows and columns. The rows contain lines of data, while the columns are topics. Each row needs to be traced to an appropriate column. Once traced, I simply check off the appropriate cell with an X. I have to make sure that each row has at least one "X".
I have attached a sample file. look at cell B8. The statement in this row reads "Issue Placard information and registration". Now, I already know what column this statement belongs to (column CT).... but I don't want to scroll over hundreds of columns to that particular column, or have to freeze any panes, to locate that cell and mark it off. I have been doing this manually and it is incredibly tedious and time-consuming. Is there a way where I can locate the appropriate cell and go to it without having to scroll? When I'm scrolling I have to not only scroll, but I have to read the rows and columns again to make sure they indeed match. I want to be able to locate the right cell instantaneously and just enter an X value.
I have a document where there are hundreds of rows and columns. The rows contain lines of data, while the columns are topics. Each row needs to be traced to an appropriate column. Once traced, I simply check off the appropriate cell with an X. I have to make sure that each row has at least one "X".
I have attached a sample file. look at cell B8. The statement in this row reads "Issue Placard information and registration". Now, I already know what column this statement belongs to (column CT).... but I don't want to scroll over hundreds of columns to that particular column, or have to freeze any panes, to locate that cell and mark it off. I have been doing this manually and it is incredibly tedious and time-consuming. Is there a way where I can locate the appropriate cell and go to it without having to scroll? When I'm scrolling I have to not only scroll, but I have to read the rows and columns again to make sure they indeed match. I want to be able to locate the right cell instantaneously and just enter an X value.