Compare Multiple Column Of Data And List Out Common And Unique Component In Adj Columns
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
ADVERTISEMENT
Sep 11, 2013
Here's how my daily report is currently set up:
ExternalInternalDifferences
123000123000
234564234564
345456345456
456567378987
565456456567
654547524564
788879565456
865478654547
745654
788879
865478
The External column contains data provided by an external vendor. The Internal column contains data compiled internally. The data in the Internal column will always contain data duplicate to the External column, but will also contain unique data not present in the External column. I would normally cut out the data contained in Internal column that is not duplicated in the External column, and paste it into the Differences column, and move the remaining cells in the Internal column up. The result would be the External column data and Internal column data would align by row, and the data unique to the Internal column is segregated to the Differences column, like so:
ExternalInternalDifferences
123000123000378987
234564234564524564
345456345456745654
456567456567
565456565456
654547654547
788879788879
865478865478
While not a difficult task for 8 rows of data, the actual daily report contains over 1000 rows, on average. I would like to set up a template workbook, where I can simply open the template workbook, paste the data into both the External and Internal columns, then have the values that are unique to the Internal column extracted from the Internal column and inserted into the Differences column.
The end product would be used daily, with differing amounts of data (from 100 rows to 10,000 rows).
View 8 Replies
View Related
May 9, 2014
Looking for some code to do a simple compare column A to Column B (row 1 contains headings) and highlight any differences.
View 3 Replies
View Related
Oct 12, 2011
Is it possible to take the information below and make one list out of columns F, H and J based on the common information in column C? For example, group all the 18s in a list, followed by the 44s.this is excel 2007
column CColumn Fcolumn Hcolumn J
Option code
18180L12369301/123/54
18180L12369301/123/54
18180L12369301/123/54[code].....
View 2 Replies
View Related
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
Mar 2, 2007
I would like to compare some keyword lists if possible.
I have a large list of Unique keywords in Col A (From A3),
This Column is called Keyword List A - Large"
I then have a keyword list in Column C (From Cell C3),,
This Column is called "Keyword List B - Small.
I then have a column called "Unique Keywords Found",, This is Col E,, with hopefully returned results being entered from cell E3 downwards.
What I would like to be able to do if possible is run a Macro that would compare all the unique words in ColA and C and return only the difference, (The Unique words not found in ColC as Col A is the "Master List")
If possible could a pop up box appear saying
===================
List A No Rows:xyz
List B No Rows: xyz
No of Uniques Found : xyz
Time Elapsed (sec): xyz
===================
I'm running windows XP and Excel 2007.
View 9 Replies
View Related
Jan 21, 2010
this is in reference to a question I asked some time ago in which I was given the following: =IF( COUNTIF(SHEET1!$A$1:$A$1200,SHEET2!A1),SHEET2!A1,"")
To summize as this is not quite working, I have two worksheets and in Column A are id#s. Worksheet 1 has100's more rows of data than Worksheet 2. I need to find the unique ids from column A in Worksheet 1 that match Unique IDs in worksheet 2.
I need to copy the row of cells from worksheet 1 where the unique id equals that u.id in worksheet 2. That row needs to be copied to Sheet 2 that has the same unique id, and after the pre-existing cells which are already present.
So for instance: if Sheet1 A100 = Sheet2 A24, then copy row A100from Sheet 1 and paste it to Row A24 of SHeet 2 start with the first empty cell- just say Sheet2 G24 as an example.
Using the formula I was given before, it finds matches, but the pasted rows are off. If the match occurs with Sheet 1 A450 and Sheet 2 A36, it will copy the value from ROW/SHeet 1 A36 instead of ROW/Sheet 1 A450
View 9 Replies
View Related
Mar 12, 2008
I need a UDF to make live easier. I have a huge excel file and I need a final function (I suppose a UDF) that lists all values from a range spanning over multiple columns and rows. I would not really like a macro, since it does not update when certain values are changed.
It's part of a bigger functionality, but I need this one, really bad, since it spans over 6000 rows as the search range, and 7000 as the search values. In short:
I got to list all the unique values. They are in specific cells on worksheet 1 (A1:C5 and A25:C31) and should be listed on worksheet2 starting in field A2. As 'simple' as that. Nothing more, nothing less, just list all unique values of a certain range, which I specify. If it can not be done, by selecting the two fields separately, it's no problem at all to list all values in eg A1:C6000. Do note that some of the cells will be blank, and I don't need blanks. I only need the actual data sets. I added a sample excel file, that shows a representation of the data. I can not upload the actual file, since it's way too big (60 MB). So a sample file should give you the idea.
Three sheets:
Data 1: some value lists multiple columns
Data 2: some value lists, again multiple columns
Desired result: the result as it should be listed.
example.xls. zip
View 4 Replies
View Related
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
View Related
Jul 24, 2014
i want to compare two excel sheets and extract common data in either sheet. For eg: If sheet 1 and sheet 2 contains some common data, i need to compare or lookup or whatever i do but i need to pull out that common data in sheet 2.
View 3 Replies
View Related
Sep 25, 2007
I have 2 columns of data looks like this:
col-a
shark
whale
dolphin
seal
fish
col-b
dog
cat
fish
deer
moose
What I'm looking for is a formula to compare col-a to col-b and create col-c to look like
col-c
dog
cat
deer
moose
View 9 Replies
View Related
Oct 28, 2011
I'm looking for a way to get a unique list from a column to a data validation drop down list. Any fancy formula or vba script to create a UDF which. Does this?
View 5 Replies
View Related
Jul 2, 2014
I have a table with 6 columns (A to F) and multiple rows each, with cells containing words. Taking the words in any one cell from each of the columns in order from A to F will form a complete sentence each time. I need a solution to display all unique possible combinations in column G.
The number of rows is different for each column. A successful result in column G has to include cells from all columns (A to F).
I searched this forum and found a few analogous questions/solutions, but nothing close enough for me to apply to my case. I tried using a concatenation formula, but I have to manually edit the formula in each cell to get all unique combinations (and that would mean thousands of times). If I just drag the formula down it will increment all cell rows instead of one cell's row at a time.
Here's an example : all possible unique combinations.jpg
View 7 Replies
View Related
Apr 28, 2014
I have a spreadsheet of 12 columns (A to L), with a list of student names under each down to row 31. Each column represents an after-school club that the child can attend.
However, a child can sign up to multiple clubs, so their name can appear in several places across the spreadsheet.
What I would like to do is have a 13th column which is an overall list of students who have signed up to at least 1 club, i.e. their name appears at least once in columns A to L.
How do I achieve this so that only unique entries are listed and duplicates are removed in this 13th column?
View 7 Replies
View Related
Feb 11, 2013
compare some list of values in order to fiind the common values.
View 1 Replies
View Related
Apr 3, 2014
I have one column which contains suppose first names & i have other two column which contains first name & last name in same sheet but like they may be having in g & h column.
so i want to exact last name of user form that column(g & h) to my first name column(a).
View 1 Replies
View Related
Jul 13, 2009
i would like to paste multiple duplicate values from unique values. Is there any way to do it in excel??
Ex:
Table 1:
3MG_HUMAN453
3MG_HUMAN44
3MG_HUMAN953
A1AT_HUMAN285
A1AT_HUMAN76
A1AT_HUMAN234
A1AT_HUMAN653
A1CF_HUMAN96
A1CF_HUMAN23
A1CF_HUMAN765
A4_HUMAN 944
A4_HUMAN 23
A4_HUMAN 755
Unique table
3MG_HUMAN100
A1AT_HUMAN78.89
A1CF_HUMAN90
A4_HUMAN 98
I wanted to add new column with these values for table 1 including duplicates. this is sample data and i wanted to replace thousands of this type.
View 7 Replies
View Related
Apr 16, 2014
How can I compare the data of two columns, and if the same, to show me the number from a third column? I upload a quick sample
View 4 Replies
View Related
Jun 1, 2007
I have two columns in Excel.
I want to compare the two columns and pull out the unique entries.
So any entries that are in both column 1 and column 2 I don't want anymore.
I just want all the entries that are NOT IN BOTH columns.
View 11 Replies
View Related
Feb 12, 2008
I have a database with 2 un identical columns: A, and B. Each cell in Column A should have an equivelant cell somewhere in column B. I want a code that could compare each cell in column A with each cell in Column B. Cells in A that don't have equivelant in B should have their values printed in column C.
View 4 Replies
View Related
Apr 12, 2012
I have a single column with multiple rows of data like this: Afirst last, Bfirst last, Cfirst last (all in one cell) etc.
I want to type some names in one cell in the same format (Afirst last, Cfirst last, Bfirst last) but not order and then check if any of those names exist in the first column and count the results of matches.
So in this example I am looking to count Bfirst last and/or Cfirst last in every cell of column A. I need to use a formula.
View 2 Replies
View Related
Jun 24, 2014
I have a worksheet which contains 2 columns which is needed to work my problem.
Unique Work ID and Description
The unique work ID are the same for each description, but there are up to 5 different description associated with each unique work id.
I'm looking for an automated process but where to start to convert the 5 rows in the unique row and 5 column for the descriptions
View 8 Replies
View Related
Feb 4, 2013
I'm trying to compile a VBA that would allow me to compare 2 columns "A" in different worksheets (same Workbook) and output any unique values to 3rd worksheet together with the rest of the values in the corresponding row.
Sheet1
A
B
C
[Code]....
Excel 2010
View 9 Replies
View Related
Nov 28, 2007
I have a long list of 2 columns containing data as follows:
col A col B
country1 date1-1
country1 date1-2
country1 date1-3
country2 date2-1
country2 date2-2
country3 date3-1
country4 date4-1
country4 date4-2
country4 date4-3
which I would need to move to get one row per country in column A with all related values from col B in multiple columns on a single Row, e.g....
View 9 Replies
View Related
Dec 21, 2009
I have two lists of components, a component from List A is added to one from List B and a total in £'s needs to be shown, simple enough I hear you say HOWEVER the LENGTH of component B is variable.
For example
Input part # ABC in A1 and £50 is shown as a total in D1, then input part # 123 in B1 and the length of 100 in C1 and the total changes to £100. Then if you change the figure to 200 the total changes to £150
View 7 Replies
View Related
Jun 30, 2014
I have a excelsheet that looks like this:
Column A | Column B | Column C
Los Angeles | Fire Dept | 3
Los Angeles | Health Services | 12
New York | Fire Dept | 8
New York | Health Services | 22
New York | Internal Services | 100
New York | Public Works | 7
Chicago | Health Services | 15
Chicago | Public Works | 56
Chicago | Social Services | 4
And I am trying to make it look like this:
Fire Dept
Health Services
Internal Services
Public Works
Social Services
Los Angeles
3
12
New York
8
22
100
7
Chicago
15
56
4
View 8 Replies
View Related
Aug 4, 2008
I have 1 workbook, with 3 sheets. Sheet1 (EVER) has 3000+ rows and 12 columns of customer information. This sheet is for all customers who have ever placed an order. Sheet2 (06-07) has 1500+ rows and 12 columns of customer information. This sheet has all customers who have placed an order in the last 2 years. Sheet3 has 1 row, which consists of the column titles (12 columns) that are on Sheet1 and Sheet2.
I need to put all customers that are on Sheet1, but not on Sheet2 in Sheet3. I have tried VLookup; advanced Filter and a number of codes in the last 3 days and have not been able to figure this out.
View 6 Replies
View Related
Oct 6, 2013
I have two columns of data and what I would like to do is have excel look over one column ( I5-I379) and for each cell that has say 30 in it I want it to return the value from a corresponding column (G5-G379), to form a separate column.......is this possible?
View 9 Replies
View Related
Jul 7, 2011
Table 1
Code:
7/4/2011AMWG16=01BLK25K_USA[a3]*042711Date a Rich Black Man By June 232350$0.19 http://www.jyxsaw.com/blackrm/index.php?t202id=2676&t202kw=AMWG16=01_0210-3
7/4/2011AMWG16=01BLK25K_USA[a3]*042711Date a Rich Black Man By June
[Code]....
I've got two tables of information here, if you scroll to the far right of table 1 and look at the left of table two, you'll see a common code like:
AMWG16=01_0211-20
AMWG16=01_0212-16
and these entries will appear multiple times as well
AMWG16=01_0212-16
appears twice and the data is different and will need to be added up
How can I:
1. Add up the data on each table with the common string such as "AMWG16=01_0212-16"
2. Take all the data that has been added up and combine the statistics, for example,
when its all done it should show
AMWG16=01_0212-16
in the first column, and then all the statistics from the two tables in the columns to the right.
I'm guesssing...
Since some entries appear multiple times, you will need to sum up all the unique entries for that value. Say:
AMWG16=01_0211-20
Shows up three times, you'll need to sum up all the values and this needs to be done on both tables
THEN
You can combine the data
I'm familiar w/ vlookup, index, match functions, but I'm not quite sure how to put this all together
View 4 Replies
View Related
Dec 30, 2008
I have a matrix with info of persons of different cities in consecutive rows, where the
city data is in column B. I would like to eliminate columnb B and divide all persons from
any particular city with 2 new rows, their city name and below a field for every column data.
*The number of employees for city would be variable.
Original data (The columns used go from A to E)
1 City_1 Employee_1 Address_Employee_1 Other data_Employee_1
2 City_1 Employee_2 Address_Employee_2 Other data_Employee_2
3 City_1 Employee_3 Address_Employee_3 Other data_Employee_3
4 City_1 Employee_4 Address_Employee_4 Other data_Employee_4
5 City_2 Employee_1 Address_Employee_1 Other data_Employee_1
6 City_2 Employee_2 Address_Employee_2 Other data_Employee_2 ...
View 9 Replies
View Related
