I have two sets of 2 columns. One column in one set is identical to a column in the other set, but they are arranged in different orders. I would like arrange those two columns side by (matching their numbers) so that I can find the average of the two non-identical columns.
View 2 RepliesI 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 RelatedI 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
compare some list of values in order to fiind the common values.
View 1 Replies View RelatedI need to find out common numbers between columns. Each column has multiple number entries seperated by a comma. For example:
Column A: "5592,12222" and Column B: "1,2,3,4,5592,123123" and Column C: "3, 4"
I need to find out any numbers that are found in more than 1 column. Hence, the result should be something like "3,4,5592". If no match is found then maybe can indicate by coloring the cell. My data is over 50,000 rows and 30 columns. I need to do this for each row one by one.
I've two sheets containing Incoming and Outgoing data. On the Outgoing Sheet, the Reply ref. shows the corresponding Incoming document replied for. I want to have the Reply Document ref. on the Incoming sheet corresponding to the document replied.
View 4 Replies View RelatedLooking 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 RelatedSo I have two columns, in column a, i have a list of zip codes and in column b i have a larger list of zip codes. is it possible to find what zip codes in column a appear in column b, and then output those zips?
View 1 Replies View RelatedI'm glad founding this great forum. I have learned a lot in the last few months.
Now I need to do a task and can't find online a tutorial for this. Here is what I need:
File A contains 23 columns with info for 300 products (rows). File B contains same columns for 280 out of the 300 products from file A, plus 40 new products.
I need to flag in file A the 20 products that are not in the file B, plus flag in file B the 40 products that are not in the file A.
The structure, columns, etc are the same for both files.
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
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
I have two worksheets containing four columns of data, example below. I need to match data found in any one of the four columns in worksheet B, against the data in the corresponding column of worksheet A, and if matched, return the result from the “Asset No” column of worksheet A (and return N/A or similar if none of the four columns could be matched)
Worksheet A
Asset Serial Server IP No.
1234511111ABCABCBlank.
45678Blank.DEFDEF12345
7891033333Blank...45678
Worksheet B
Asset Serial Server IP No.
Blank.22222XYZXYZBlank.
45678Blank.Blank...12345
Blank.Blank.Blank...45678
In this example, I would expect to return the result 45678 against row 2, & 78910 against row 3, and receive a “not found” in row 1
I tried using IF & OR in an array formula, but could only get to a TRUE or FALSE result (was not able to reference the Asset Number).
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
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.
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
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].....
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.
I want to calculate the most common text value for groups. I have a table with a lot of records like an example below.
ID Code
1 HB
1 KU
1 KS
1 LV
1 KS
2 LM
2 KS
[Code]....
I want a table like below:
ID Code
1 KU
2 KS
3 KS
How can I do it in Excel 2010?
I work for a charity and I have to cancel the donations of people whose credit card donations have been declined in three consecutive months.
If in Column A I have a list of donor IDs whose credit cards were declined in Jan 2008, in Column B I have a list of donor IDs whose credit cards were declined in Feb 2008 and in Column C I have a list of donor IDs whose credit cards were declined in Mar 2008, is there a way of showing in a fourth column which donor IDs were common (repeated) in Columns A, B and C? I would have a title for each column in A1, B1 and C1, and also the column where the repeated donor IDs would be displayed.
Is there a code or some way for me to sort 4 columns together? I want to sort a list of employees and for each employee theres a column with their Lost Business, customer satisfaction, and two more columns. I want to sort all the columns at the same time so that the best employees overall will go to the top of the list and the worst ones will be at the bottom. Whenever I use the sorting feature it does each column independant of the others so everytime I sort a new column it just moves around the last one I sorted.
View 14 Replies View RelatedIm not entirely sure about this but it seems like if I have data in columns A - Z and sort in one of them, the data in Columns AA - AZ does not move accordingly rather it stays fixed.
View 4 Replies View RelatedI have a spreadsheet that is populated my Concatinating data from other worksheets. Some of the results are numbers, some is data while others are blank spaces and othersare set by the concactenating default of "" when the criteria is not met. I need to sort these colums. However it seems that the default "" from the formula is not a BLANK or a ZERO or an empty cell.
Those cells will place themselves at the top of the sort. I need to eliniate them. I have copied and pasted as values but that has no effect. Ironically I can identify the cells with an if statement so what I need help with is generating a macro that will cycle thru the range of results and delete the approriate cells.
I have tried the following:
lr = Range("A1").End(xlDown).Row ' Last Row
lc = Range("A1").End(xlToRight).Column ' Last Column
For Each cell In Range(Cells(1, 1), Cells(lc, lr))
cell.Select
AC = ActiveCell
If AC = "" Then Selection.Delete Shift:=xlUp
Next
But this does not work as it cycles top to bottom and leaves behind 1/2 the problem cells.
I have an issue with some data that I need to sort into several columns. Basically, I have a column that has data listed in each cell like this; A 567 T 1 D3. What I want to be able to do is sort that data into several columns. I am not sure how to do this at all. I have attached my data to be looked at. I have already started the process but manually, and I don't feel like having to do this manually as this is very time consuming considering I am trying to sort the data into ~1927 rows and 5 columns.
View 2 Replies View RelatedI need help with either a formula or macro for sorting data into specific columns. I need the entries under the headings Ar,Bj... to be sorted into the correct columns. To add to the problem, the data may not be exactly the same as the heading.
Attached is an simple example of a spreadsheet where the top is the original and the bottom is what I need the final outcome to be. The data is pasted from a different spreadsheet and will be changing each time.
I have a potential of 5 columns of numerical data (simple number entries) which are entered manually in no particular order.
Is there any way of sorting the data so that it is presented in numerical order (smallest to largest) starting with the smallest figure at the start of column 1 up to the largest figure at the end of column 5.
I have three columns, one column is time every two seconds with data associated that time, and one is time every minute with an associated tidal height.
I want to sort my data so that for every 2 seconds I have an associated tidal height for that minute.
eg:
what I have:
Time Time Tidal Height
(2 sec intervals) (min) (meters)
1:15:00 1:15 1.342
1:15:02 1:16 1.221
1:15:04 1:17 1.115
...
1:15:58 1:43 0.024
1:15:60 1:44 0.012
1:16:00 1:45 0.008 ....................
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.
How do I sort multiple columns at once? In other words, I have a chart that is a series of 1s and 2s, and I need all of the 1s to drop to the bottom, so that I can do a rudimentary chart in spreadsheet form. My chart has dozens of columns like so:
1 2 1 1 1 2 2 2 2
1 2 1 1 2 1 1 2 2
2 1 2 2 2 2 2 2 2
1 1 1 2 1 1 1 1 2
How do I get the entire range sorted to look like this, without having to do each column individually (hours of work)?:
2 2 2 2 2 2 2 2 2
1 2 1 2 2 2 2 2 2
1 1 1 1 1 1 1 2 2
1 1 1 1 1 1 1 1 2
I have some data that is 300 rows x 43 columns.
I'd like to sort the 43 colums by using some of the rows. Is this possible using normal excel?
Alternatively is there a simple way to sort the colums in VBA?
I have data in two columns
Column A has values with names like -
John, Paul, Hena, Kumar, Paul, Hena, John in different rows
Colum B has day values - eg , sun, mon, tue, wed , thu, fri and sat
I want to insert a new column C with formula that returns
1. for every John, concatenate of all the day values adjacent to row of John eg "sunmonfri"
Similarly for all the values in Column A, Column C should reflect the concatenate values of Column B.