Find Common Values Across Multiple Sheets Based On Unique ID
Oct 24, 2013
The old thread is here: [URL] ....
There are three sheets in the workbook, Project, Tasks and Details and the expected resulting sheets are RESULT, In_Tasks_but_NOT_in_Projects and In_Details_but_NOT_in_Projects .
But now what I am looking for:
1. Copy the Projects data as is in the RESULT sheet.
2. Then in the Tasks sheet, if the ID matches paste the matching rows under the data from Projects (as in the result sheet with Orange colour)
3. If the ID is present in Tasks but NOT in Projects then copy it into the In_Tasks_but_NOT_in_Projects sheet.
4. Then If the ID and the Name in the Details tab matches with the data in the RESULT sheet then paste it under the ID and Name (as in the result sheet with Green colour)
5. If the ID does not match the ID in the results sheet then copy that row into the In_Details_but_NOT_in_Projects sheet.
The result of the current macro that RHCPgergo worked with are in the last sheet.
The formatting and colour of the rows doesn't matter, it is more of nice to have.
View 14 Replies
ADVERTISEMENT
Jul 8, 2014
I have 6 worksheets in my file. In sheets 1-5, column A2:A26 list people's names. Some people's names appears on more than one sheet. Not all cells are populated with a value.
ex.
SHEET1
COLUMNA
Bill
[Code]....
My attempt was... =INDEX('Week1:Week5-!$A$2:$A$26,MATCH(0,COUNTIF($A$1:A1,"Week1:Week5"!$A$2:$A$26),0))
where the sheets were Week1-Week5 and the values on each sheet was A2:A26. But I think there's an issue with Excel being able to 3D reference for these types of functions.
View 3 Replies
View Related
Jan 23, 2006
I am trying to compare multiple column in a worksheet to find
common component in all the columns and what is unique to a particular
column only. And list the results/finding in adj column. What i am
trying to accomplish is something as below.
BEFORE
Sheet1 Sheet2 Sheet3
Column2 Column2 Column2
02-1234-12 07-1234-12 02-1234-12
04-1234-12 03-1234-12 02-1234-12
05-1234-12 02-1234-12 06-1234-34
AFTER
Common to all Unique to sheet1 Unique to Sheet2
02-1234-12 05-1234-12 07-1234-12
View 14 Replies
View Related
Feb 11, 2013
compare some list of values in order to fiind the common values.
View 1 Replies
View Related
Dec 11, 2013
I have a fairly large timecourse dataset and I need to find all common values within all 3 columns. Also, when I find these 'common values' is there a speedy way to retrieve data in the same row that is associated with these values, instead of going back one-by-one and copying and pasting beside the value that the function has returned?
View 10 Replies
View Related
May 10, 2007
I have two lists, one is 15,000 records, the other 100 records. I want to find the common records between the two.
I am using this formula currently, but is returning an incorrect result--
=INDEX($B$2:$B$11157,SMALL(IF(COUNTIF($M$2:$M$100,$B$2:$B$11157)>0,ROW($B$2:$B$11157),1000),ROW()-ROW($P$2)+1)-ROW($P$2)+1)
this is the record number in M2
BU1HAD80
it is returning
BI1METBRSM
as the common record. This formula is from Joseph Rubin's book F1 Excel Formulas and Functions
View 9 Replies
View Related
Jan 21, 2014
I have a large sheet (several thousand rows and growing) - see a small cut of the data. The columns following on from this have a list of people's names, hence why a lot of the data repeats (as a number of people attended each program).
Program
Clinic Title
Start Date
[Code].....
formula that will automatically give me that answer?
View 2 Replies
View Related
Apr 24, 2013
Problem description: I need to put together twelve arrows each comprised of four components; field point, front insert, shaft & fletch and nock. Therefore, I have forty-eight components in an Excel table. Each of these components may differ slightly in weight. My objective is to mix and match all of the components to create as many arrows as possible of the same exact weight.
Two questions. 1) How should I be thinking about setting up the problem? 2) Which Excel function returns the results I'm looking for?
Total weight
Point
Insert
Nock
Shaft
?
100.1
109.7
20
179.1
100.1
110
View 5 Replies
View Related
Aug 23, 2013
I have 2 sheets that i need to find what the same valued cells are.
1 sheet has names and i need to find the rows where those same names are on the 2nd sheet.
The formats are different (upper-lower case) and there is additional text as part of the cells in the 2nd sheet. (i.e prod123.web.corp.com) and i just need to find the prod123 parts.
View 9 Replies
View Related
Feb 22, 2011
I'm having a problem with a spreadsheet in Excel. I have in column terras, codti problem and several cells that are repeated. I'm stating that terra appear only once, the information in column each issue should appear in different columns with the sum of how often they appear and codti according to terra.
In excel is best illustrated what I mean!
In total, 5267 lines and need to do this with all. You can do this in excel?
View 4 Replies
View Related
Aug 22, 2013
SM extract 8.21.2013 Cust-sample.xlsx
I want to highlight the cells under System Name (col 1) in sheet 1 that are found in Host (col 1) in sheet 2 "OCPtabvHost"
The text isnt in the same format in the 2nd sheet (it is lower case and has additional text). I need to find the duplicate roots
View 8 Replies
View Related
May 27, 2014
I have an excel workbook with 60 sheets (each contain data in the same categories and in the same column locations, just different information on each sheet). What would the VBA code look like if I wanted to manually enter the find and replace values and perform the function (find and replace) across multiple sheets in the workbook?
View 5 Replies
View Related
Dec 4, 2013
Find the most common occurring string in my spreadsheet based on a lookup value. For example, if my table is as follows:
Capture.PNG
- I want to calculate the most common value chosen by user 234 (A in this case), user 524 (B in this case)... and so on.
- I want to calculate the percent of the chosen value. So in the previous point, A was chosen ~67% of the time by user 234... and so on.
I'm not sure which formula to use. I am able to find the most common occurring value in column B using the formula below (which I found here), but how can I do that for a specific value, a user ID in this case?
View 4 Replies
View Related
Mar 6, 2014
I have a different worksheet in the same workbook for every "gig" that I book. Each gig contains the name of the musicians I staffed on the gig along with their salary+expenses For taxes, I need to summarize into a new worksheet how much each musician made. Ideally i'd see not only the total per name but itemized. So if 1 guy did 3 diff gigs, I'd see each row pertaining to his name.
Lastly, I'd like to include names that do not have multiple records because it's possible there is inconsistencies with the spelling of certain names.
View 3 Replies
View Related
Apr 19, 2008
I have multiple worksheets with multiple varying columns with varying rows. My one constant is the product_id. I want to merge all worksheets into 1 worksheet based on the product_id's. Here is my example:...............
It has to consolidate all of the column names from all of the worksheets into the final worksheet, then take all of the rows and put the product_id in the product_id column and put the other data under the appropriate columns. Some columns will end up blank where they may be a column in worksheet 2 but not worksheet 1.
View 4 Replies
View Related
May 26, 2014
Currently I have it setup to copy rows to a sheet "Report" based on a single cell value. But now I need the same thing but have it copy the rows based on 2 cells values to sheet "Report". So for example I wanted to copy and paste each row in my workbook that contain values in Columns N:N that contain the value "Test" and in columns AB:AB that contain "1".
Sub copyagain()
Application.ScreenUpdating = False
Dim sh As Worksheet, findThis As String, fAdr As String, fLoc As Range
findThis = "1"
[Code]....
View 9 Replies
View Related
Aug 18, 2009
Look up a match on sheet1 from individual cells from sheet2, and if match copy and paste to sheet3, and i need this to be done for every cell in sheet2. Sort of a CTRL-F type of deal but for about 3000 individual cells on sheet2. The sheet1 contains about 10 columms with about 10000 cells all together. so to do this individually its just not posible.
View 5 Replies
View Related
Oct 8, 2007
I've got no clue about all this, but I've had to get specific formula examples and fill in the blanks in order for my timesheet to work. There's just one final problem if somebody could please help.
This is a timesheet for a 5 day work week. I need to count the number of unique log numbers for a specific activity. The log numbers counted must be unique across the entire week, not just for each day, which means I want the formula to count the unique log numbers across multiple sheets.
The formula also has multiple conditions. I got 2 columns. The first part of the formula needs to verify a word, say, "split" and if it does it checks the adjacent cell for a unique log number. If both arguments are true, it counts the log as 1 unit.
Here is a working formula for only one page.
=COUNT(IF(D4:D29="split",IF(FREQUENCY(C4:C28,C4:C28)>0,1,)))
Here's 2 problems with this formula:
1. I will count if it encounters a blank cell in the Log numbers the first time (which will happen as not every activity we do has a log#), but it will stop counting if it encounters a second blank cell.
2. I don't know how to make it work across several sheets.
This is an alternate formula which works and skips the blank cells, but I don't know how to add the multiple condition of "split" and to have it work across multiple sheets. I just copied it Microsoft. As I said, I don't understand it, I just fill in the blanks.
SUM(IF(FREQUENCY(IF(LEN(C4:C29)>0,MATCH(C4:C29,C4:C29,0),""), IF(LEN(C4:C29)>0,MATCH(C4:C29,C4:C29,0),""))>0,1))
View 11 Replies
View Related
Nov 3, 2013
Extracing unique values based on two lists(2 sheets).
My scenario:
Sheet1 has 2 columns with one million lines. (Master sheet)Sheet1 ColumnA=serial numbersSheet2 has 200,000 lines with same format. columnA= serialsheet2 has some new serials and some existing serials
Serial is 18 digit number saved as text (dunno of that matters)
Solution:
how can I get vba to check serial agaisnt serial ( Sheet2 ColumnA against Sheet1 column A)extract all new serials (unique of sheet 2 only)unique lines to export to new sheet or replace sheet2
View 5 Replies
View Related
Oct 13, 2011
I am trying to create a macro that will create a unique list of the values in the same column across 5 worksheets, "Question1", "Question2", etc, and place the list in a new worksheet, "Summary".
The values are in column A in all of the worksheets, and the number of rows in each worksheet will change each day. I need the unique list to be added to the sixth worksheet, "Summary".
Also, after the list is created on the Summary worksheet, I need a count of each field by worksheet and a total. This is how I currently have the Summary set up:
Part Type|Total|Question1|Question2|Question3|Question4|Question5
Name|=SUM(C2:F2)|=COUNTIF(Question1!$A:$A,A2)|
=COUNTIF(Question2!$A:$A,A2)|=COUNTIF(Question3!$A:$A,A2)|
=COUNTIF(Question4!$A:$A,A2)|=COUNTIF(Question5!$A:$A,A2)
View 6 Replies
View Related
Jun 2, 2009
I need to be able to pull information from 5 columns on 5 different pages, and pull all of the unique names out of it. From there, I need to create a list of all of these names. More often than not, this could only be three names, but I still would like this to be automatic because eventually it could be 50 or 60, and manually editing this list would get tedious. Lastly, is it possible to have a list create cells? For example: I have information on row 4, this one will start on row 6. My next group of information starts on row 14. That leaves 7 rows for information. I don't want to leave a large gap at the top of the page, so I'd like to be able to create cells if the list I'm trying to create has more information on it. Seems plausible in my mind, but I'm still new to excel.
View 14 Replies
View Related
Jun 24, 2009
I have a spreadsheet that holds customer information. What I want to do is find how many customers there were last month. I have a cell (C1) which has last month displayed as 2009/05. A1 holds the customer ID and B1 holds the date they used us. Each customer may has used us many times and I'm having a nightmare trying to solve this.
View 8 Replies
View Related
Aug 25, 2009
What's the formula that will search a column of values and "pull out" only one of each unique instance? The expected result, then, for the following data-set would be 4,5,6,7,8,9.
4
4
4
4
4
5
5
5
6...........
View 5 Replies
View Related
Jan 22, 2009
In an excel sheet i have around 10 columns.
I filtered the data based on the column1 using vba. Then after that, the filtered data in column2 have repeat finance accounts. What i need to find the unique finance accounts under the column2.
Is there any methods to find the unique value under the column2?
View 9 Replies
View Related
Mar 5, 2014
I'm trying to set up a macro that will capture all the unique values in a column, and then create a new sheet for each value. What I've got so far technically works, but it's also adding an extraneous sheet (with a generic sheet name, not the specified name I'm giving the sheets) and also giving me "Run-time error '1004': Application-defined or object-defined error" when it runs.
View 3 Replies
View Related
Mar 14, 2014
I'm running into an issue trying to calculate unique values in a Data column based on a few variables in other columns.
My current formula in Summary tab D4:D19 is
{=SUM(IF(FREQUENCY(IF(Data!$I$3:$I$66<$E$1,IF(Data!$A$3:$A$66=$H$1,
IF(Data!$C$3:$C$66=A4,ROW(Data!$I$3:$I$66)))),ROW(Data!$I$3:$I$66)),1))
+
SUM(IF(FREQUENCY(IF(Data!$I$3:$I$66<$E$1,IF(Data!$A$3:$A$66=$I$1,
IF(Data!$C$3:$C$66=A4,ROW(Data!$I$3:$I$66)))),ROW(Data!$I$3:$I$66)),1))}
This is currently counting the number of times a date value (data column I) appears for that name (A4:A19) in the data when meeting all of the conditions. I need it to instead count the number of times a unique date appears for that name with the additional conditions met (which all appear to work fine).
The results in the pink highlighted cells (Summary column D) should be:
Names starting with A - 3
All others - 2
I've left some other columns in the data with X's so that I can easily convert this back to my working spreadsheet.
View 2 Replies
View Related
Aug 26, 2013
I have an Excel Spreadsheet (2007) that contains over 500,000 records that shows Electric meter usage per month over a 24 month period per meter. What I want to be able to do is to select a meter row per number and copy is to it's own worksheet. At the end - I want to be able to have a seperate worksheet per electric meter number - that I can create a graph. If I go through all 9000 meters and copy and paste into a different worksheet - it will take me weeks to do manually. How can I do this automatically?
View 1 Replies
View Related
Dec 14, 2009
I need to find rows in my large worksheet that have 2 vendor names for a single vendor ID. Each vendor should only have 1 vendor ID.
Is there a way to highlight rows that have a single vendor ID for more than 1 vendor name?
View 9 Replies
View Related
Apr 9, 2009
Is there a way i can have a macro find the unique values in these 3 columns and highlight them yellow.
They should find uniques using all 3 columns, not find them within each column.
I included a file which shows what i start off with and what it needs to look like. Also if it matters the contents in the cells are from links. ( i know that matters because you have to put look in values in the code ).
View 9 Replies
View Related
Oct 28, 2007
I have scoured the Excel boards but cannot find what I am looking for. Need to do a formula approach (No Pivot Table) lookup on a dynamic table that is not necessarily in order and may have blanks in some cells or whole rows. What I would like Excel 2003 to do is report (extract) the all first occurrences of each organization number that has at least one person with a "Yes" in a corresponding column in the same row. I also need the number of persons that have "Yeses" in each of those identified Org Nos .
Data is laid out as such:
Org No. Name Not in Compliance
8631Steve Yes
8631David
8631Nick
8631Tom
8631Susan Yes
8701Peter Yes
8701Diane Yes
8701Mary Yes
8879Don
8879Bernie
8879Wayne
8900David Yes
The answer is
In one row list the org #'s not in compliance ("Yes"). In the row just below it the number of persons:
86318701 8900
2 3 1
I have a helper column that counts the number of unique org nos, but cannot tie that to those that have Yes in the other column.
In column before Org No i have =IF(COUNTIF(b$33:b34,b34)=1,MAX(a$33:a33)+1,"") copied down.
View 9 Replies
View Related
