I have a list of last names in Sheet1!Column A. I want to see if any of those exist in Sheet2!Column A, and if it does I want to post the data from Sheet2!Column B into Sheet1!Column B.
View 2 RepliesI tried both IF and LOOKUP and failed. I'm trying to search for values from one worksheet and identify whether or not those values exist in another worksheet. I attempted the following lookup in field A2:
=LOOKUP(B2,Sheet3!A$2:A$914,Sheet3!C$2:C$914)
B2 (thru B5000 or so) contains values I want to search for; sheet3!A$2:A914 is where I want to look and column C of that same sheet, entered the text "Yes" in an attempt to have the results list "Yes" for hits and N/A for misses. (All fields are text.) I copied the formula all the way down the sheet in column A. The result it is returning is N/A in A2 and Yes in A2 -to the bottom, which is incorrect.
I have 2 arrays that look like this:
Mercedes ________________Mercedes
BMW ____________________Fiat
Fiat _____________________BMW
VW _____________________AUDI
AUDI_____________________Jeep
Jeep_____________________Porsche
Porsche __________________Ferrari
_________________________Lamborghini
_________________________VW
As you can see, list 2 had some additions (Ferrari,Lamborghini) and a different overall order. I want to input the 2 additions on list 1 right after the last cell(Porsche). List 's 1 order cannot be changed. I have to do it on Excel 2003.
I'm wondering how I can unlist listed objects if a list or lists exist on a sheet.
I tried the following , but it doesn't work.
I have a large worksheet (about 9,000 rows) with lots of vlookups to another sheet.
It is possible that some of the lookups will return '#N/A' but it is laborious to go through all the rows to check.
Is there a way to have a macro to look through that part of the worksheet, and to pop up a message box with the cell reference of the first error it finds? (I'm sure there is a way! I just dont know it)
How do I lookup a table , eg. E4:G16 to see if that table contains a data.
I tried {=search(A4,E4:G16)} where A4 is in cell F10 but returns Value error.
The yellow highlight is where you input the data needed. The problem is it doesn't show the result when the letter has a duplicate. For example, when I enter "a" in the first input cell(input letter) and "1" in the next(input number), I get the result("a1"). However when I input "a" in the first and "2" in the second, the result just shows a blank cell, and so forth.
View 9 Replies View Relatedhave a formula or something along those lines that will look for the data you type in to the selected cell and will show you on how many workbooks it has been entered in. For example, I have workbook a, b, c, d, e, f. all have 12 sheets. On these 12 sheets there is a place to enter the serial number of an item. What I would like to be able to do is on a different workbook be able to type in a serial number and have it show which workbooks it has ever been typed in and its location on said workbooks. and if possible the results of the item which would be whether it was accepted or rejected( this bit is not totally must have. but the workbook and sheet location is a must.)
View 1 Replies View RelatedI have created a form that will be used my many people. The first person will enter their information and click a button to transfer their data to a second workbook, as well as send the workbook via email. At a later time, someone else will add more data to the emailed form and transfer this to the database in the second workbook. What I need is to be able to find the row in the second workbook containing the data transferred the first time and add the data that was put in later to the same row as the original.
View 7 Replies View RelatedI have a spreadsheet that has the following
I have 30 teams with 15 players each
I have teams in rows and players columns with their ID and then name so B2 would be 444 and B3 would be Johnson and B4 would be 429 and B5 would be Smith
What I want to do is run a formula that counts the duplicate ID's not names and then shows them in a list like below
Duplicate ID, Number of times in worksheet
444 , 23
411 , 21
211 , 15
How I can do this.
I have two columns B and C - In column B are the names of people. (12 names in this list) and in column C is a number from 1 - 4 (each person is in either team 1, 2, 3 or 4)
I have a drop down list in Column A which links to a small table (1 2 3 and 4) so the user can choose which team. I need a formula to then list the members of that team, when it has been selected.
I was using a Vlookup command, but this only works for the first person on the list, if i drag the formula down, it is still the same person. I need to show a list of all the members of that team.
I have been going around in circles trying to work this one out... I have set up my "Work Packages" sheet ready to use with an excel timesheet. I have entered all ......
View 9 Replies View RelatedI have done a spreadsheet that shows leave for staff. At anyone time there can be only 3 managers and 4 staff off as a maximum and the cell value for the day is entered as "AL". Therefore on a given day if someone tried to enter a fifth "AL" for example in the staff section a pop up displays stating they have reached a maximum.
View 4 Replies View RelatedIm trying to come up with a Macro that will perform a =SUM() function across multiple workbooks. Some of these workbooks may exist, some may not.
For example, Summary.xls has a =sum() formula that picks up data from wbk1.xls
wbk2.xls
wbk3.xls
etc, all the way to 100
Today i only have wbk 1, 2 and 3 but i know everyday more wbk's are added so i want to create a sum type function to accomodate the sbk's that may be created at a future time.
Right now when i try it, I not only get REF/# but on opening Summary.xls I get a popup that asks me to define the missing wbk's
I need to find the unique column values and sum the next column values. i,e
ID XXX
1 100
2 200
1 100
3 500
4 600
2 200
--------------------
Total 1400
I have a 'master' spreadsheet containing over a thousand filenames. I have been given another spreadsheet with just a couple of hundred filenames on it and have been asked to find out if the filenames on this 'small' spreadsheet appear on the larger/master one.
Is there any way (by formula or by macro code) that I can do this easily? What would be great is some way of Excel colouring in the filenames on the 'small' spreadsheet that also exist on the master one.
I am trying to use the function Match into the Index function but as a Result I have nothing than N/A#, I would like to be sure that I'am using this function in a proper way
Workbooks(filename_appli).Activate
Range("B" & dercell_appli, "C" & première_ligne).Select
Set Rango3 = Range("B" & dercell_appli, "C" & première_ligne)
Range("C" & dercell_appli, "C" & première_ligne).Select
Set Rango4 = Range("C" & dercell_appli, "C" & première_ligne)
ws.Activate
dercell_unit2 = Range("C65500").End(xlUp).Row
For y = 4 To dercell_unit2
Coincidir = Application.Match(Rango4, Cells(y, 3), 0)
Cells(y, 2) = Application.Index(Rango3, Coincidir, 1)
Next y
Each column represents a user and each row represents an application the user has. I would like to extract all the applications that are common to all the users and place it in an arbitrary column.
For example
User1_____User2_____User3
Office_______Office_____Office
Adobe_______Adobe____Adobe
Flash________Flash_____Snake
Firefox_______Firefox
Solitaire
The above list should generate Office and Adobe since every user has Office and Adobe.
I'm trying to simplify a template we use regularly at work. On the front end page, the one that eventually gets turned into a PDF and sent to a customer, I have a selection of drop downs, which I am pretty comfortable with. I also have some other fields that are populated by vlookups, and again, I have no problem there. What I am struggling with is creating a second drop down list that only has options relative to the first drop-down list. From what I gathered by searching on this, I may have to change the format my data is in, note that I have a pretty basic knowledge of excel.
Master Data (from where I want all the drop downs to come from) - see attached screenshot.
Capture.JPG
So, on front end, I have a drop down that selects from column A. Beneath that there are two rows that auto-populate depending on what company was selected in the drop-down.
What I want is a second drop down list from D, E & F.
So, If I select ABC Limited in the drop down menu, the second drop down should only have John, Jim & Jane as options.
Is it possible to do this with the data in the format I have, or would I need to change the format? I have +/- 500 entries to do, so obviously I'd like to not change my format if possible.
This is a re-submission of a question previously submitted because the title for the first submission was so poorly worded.
I have a column that has numerical values in random order. I want to locate the first occurance of a value in that column.
I have unsuccessfully tried an Index-Match function - apparently unsuccessful because the values must be in ascending or descending order?
I have a challenge which I am trying to solve without VBA and I am uncertain how to approach
If I have the following spreadsheet consisting of three columns:
FieldA, FieldB, FieldC
A........3................
B........5........6......
C.................7......
D.........................
E.........................
F........6........4......
The result I would like to achieve is
FieldA, FieldB, FieldC
A........3................
B........5........6......
C.................7......
F........6........4......
So, it means I would like to return the rows where Columns B and C have a value. Is this possible with strictly formulas?
In the attached spreadsheet I would like to locate the peak values of the numbers in column "F". I don't know if this is best done with a series of functions or by using a macro.
Column "F" translates to the light blue line on the attached chart. Column "F" (MACD) is the difference between a 12 week exponential moving average (EMA) and a 26 week EMA.
I would like the peak values from column "F" re-stated into column J,K,L or M. Ideally I would like to find the highest peak that occurred in a rolling 12 weak period. So not all peaks would need to be posted. By doing this I would only be identifying longer trends .
I'm using Excel 2007. 25 years experience using Excel and functions. Limited experience with Macros though long ago I used to write C code.
I am trying to determine if the value in cell Y2 also exists anywhere in cells AE2:QE2.
My initial stab at it is
[Code] .....
This results in a #VALUE! error (I am pretty sure that's because excel doesn't like the AE2:QE2 part).
I would do a formula-driven conditional format for this, but I have 2000+ rows that need to be acted on, so that's not feasible.
Is there any way to have excel check if the Y2 value exists in AE2...QE2, cell by cell?
I'm trying to continue on this post: Populate Cells After Choice From Drop-Down
I'm trying to expand on CNL's idea. What I need to do is create a spreadsheet that autopopulates two blocks based on the dropdown box that preceedes it. I've got it to partially work, but can't figure out the next step. The differnce between my sheet and CNL's sheet is my drop-down is an indirect data validation.
The easiest would be to have my data in one long list, but my goal is to make it easier on the user to find the specific procedure (I'm trying to build a budgeting spreadsheet for a medical office). So in box A15 there is a list of the different service area, which gives a list of choices in C15 based on the choice selected in A15. My goal is to have G15 and G16 list the cost and charge respectively.
M1 - O3 is the data for Radiology
Q1 - S4 is the data for Labs
U1 - W4 is the data for Office
I have 400 source files containing (among others) 8 sheets with daily results: "Fri 23", "Mon 26", "Tue 27", "Wed 28", "Thu 29", "Fri 30", "Sat 31 (if applicable)", "Mon 2".
Each sheet contains also:
State - D1
Role - D2
Staff ID - D3
Date - D4
Activity group name in column A (starting from row 8)
Activity type in column B (merged with C and D) (starting from row 8)
Activity time in columns E:GV (starting from row 8). Usually, there is none or only one value in whole range (e.g. E8:GV8). But sometimes there are two values.
Customer ID in row 6 (value appears only if time was reported in E:GV range)
CC Number in row 7 (value appears only if time was reported in E:GV range)
It's all about transferring values from all daily sheets in all files (.xls) sitting in folder C:WADFinal to one simple table (WAD_Consolidation_file.xls, sheet "Consolidated") consisted of 9 columns: Staff ID, Role, State, Date, Activity Group, Activity Type, Minutes, Customer ID, CC Number.
Additional note if two values exist in the same row they should be copied as two separate entries to consolidation file.
I want to do, is search column A for claim numbers that match. When I do have a matching claim number, I want to concatenate the original cells ownership field with the said matching cells ownership field (or move into a column in the same row, I can always concatenate later). Once that is complete, I want to delete the row I took the information out of.
I want to join this data in ArcGIS, but as of right now, it's not a 1-to-1 relationship, so only a relate works. That doesn't help me as I want to display claims by ownership, and this can vary per claim. Company A may have 100% on one claim, and then split another claim 50% with Company B.
This causes a double entry on the claim field in this current spreadsheet I have, which requires me to clean it up by making multiple columns of ownership vs. an additional row for shared ownership.
My problem:
Column A Column B
1235555 Company A (50%)
1235555 Company B (50%)
1235556 Company A (100%)
1235557 Company A (33%)
1235557 Company B (33%)
1235557 Company C (33%)
What I would like to see
Column A Column B Column C Column D
1235555 Company A (50%) Company B (50%)
1235556 Company A (100%)
1235557 Company A (33%) Company B (33%) Company C (33%)
im trying to get the lookup answer based on two values the current formula i have is
=VLOOKUP(AND(A1,B1),Sheet2!$A$1:$C$31,3,FALSE)
which naturally returns N/A... i might even be using the wrong formula?
1 workbook, 2 worksheets (or tabs). On tab 1, I want a formula/alert that tells the user if any duplicate values exist in Column A of tab 2
Tab 2, Column A, has Unique ID's (6 digit numeric values)
The user manually inputs the ID's on new rows in Column A
Row 1 is reserved and in use for something else
Row 2 is my header, so cell A2 says "ID"
Row 3-623 currently contain unique ID's
When the user inputs a new ID into cell A624, then they return to Tab 1, I want my formula/alert on Tab 1 to tell the user that they have duplicates in Column A of tab 2. I know the Conditional Formatting, but if the user copies in 100 new values, they won't necessarily see the highlighted cells. My tab 1 is my "checks and balances" and the last place the user is suppposed to look to ensure that they haven't created any duplicate ID's. If the user sees a warning message that says duplicates exist, then I'll tell them that they need to look at column A (for cells that have been conditionally highlighted).
One issue that I'm running into with the conditional highlighting is that I want cells A3:A1048576 to already have the conditional formatting - this way when the user inserts a value into Cell A624, then A625, etc they conditional formatting is already there. Right now with data in cells A3:A623, cells A624:A1048576 are all highlighted with the Red/Bold Red Font (which is okay I guess), but ideally it would be nice to not count 2+ empty cells as duplicates and I'll have to have my formula on Tab 1 not include the blank cells.
I DO NOT want to use the Remove Duplicates feature of Excel 2010. If I remove them I could be removing data in columns B, C, D, etc that belong to the Unique ID. I just need the user to be told in Tab 1 that they DO have duplicates and I'll train the user how to research this and fix it.
The reason I want to look for duplicates in the entire Column A is because the list of Unique ID's will grow over time.
I have a table of data (say Column1 to Column 5) with multiple rows.
Column 1 to 4 will have the lookup values in multiple rows and Column 5 data should be picked up using vlookup or other lookup function.
I managed to somehow bring all these lookup values in (Column 1 to 4) in a single column in another sheet. I am now trying to use some lookup or other functions to match this single column and pick column 5 data in original sheet. Result i am expecting is lookup value in first column and next to it column 5 value.
It is basically a lookup wherein lookup value is spread over multiple rows and columns and result column is fixed. I tried using vlookup, but lookup value column and column number had to change every time when i moved from column1 to 4.
I have 4 lists:
Job #
Job Title
Client
Job Location
I would like to select the Job # in Cell B2 (Job # list) and have Cells C2 filled in from Job Title List and D2 Filled in from Client List and E2 filled in from Job Location list.