I have two to four columns with this content: w2s3esus who translated means 9 chars (the number before the letter is the time it appears). I want to compare the columns and pick the shortest one automatically like so:
column 1: w3s4nusun = 12
column 2: w5s2esus = 11
column result: w5s2esus
See data in the attached image in Col A thru Col D. In Col E, I want to populate the max value for the same part # from Col C or Col D depending upon value in Col B.
I have included a sample of the expected results in Col E for illustration purposes.
For example Row 3,5,6 are all part # 1 with Col B ="Y", suggesting they are interchangeable parts, therefore, I would like the formula to have ability to pick 10 (since its largest between 3,10,0) from Col C.
Capture5.PNG
i want to pick data from every 2 columns and arrange it vertically, one under the other ;
sample data:
A 579751 579800 52151 52175 126721 126750
B 546451 546500
C 608971 609000 508081 508110 548941 548970
E 962701 962750 24851 24875
desired outcome:
A 579751 579800
52151 52175
126721 126750
B 546451 546500
C 608971 609000
508081 508110
548941 548970
E 962701 962750
24851 24875
I have a list of data which is not in any particular order....
View 9 Replies View RelatedI am working on a project to clear festival events with simulation. Therefore I need a grid of Excel cells (the field), let's say 11x11 (B2:L12). And the exit is in cell A7.
I am looking for an algorithm which fills this field with distances to the exit. For horizontal and vertical movements the distance is 1, and for diagonal movements the distance is 1.5
Say I made a class Coordinate which has two points, X & Y I declare a variable point, as the location I am in (the active cell) and a constant exit (the coordinate of the exit)
And then I would like a function in VBA which does:
[Code] ......
How to approach this?
I want to have them in order of shortest price (favourite) first with the relevant horse's name in the adjacent column. I have just discovered vlookup which seemed perfect - until I discovered that when there are two or more identical prices it only returns the 1st horses name.
Is there any way I can use vlookup to do this (with more than one identical value)
I want to use a User Defined name look up its corresponding X/Y Coordinates , reference a list organized by categories, then go through each name in the list finding each's X/Y Coord Calculate distance.
SQRT((X-X)*(X-X)+(Y-Y)*(Y-Y))
where X and Y are Coordinates
& Return the name of the smallest Value.
I have a list of project numbers that is over 7000 rows long [example attached].
Many numbers are repeated. This list comes out of a financial reporting system.
I need to extract a list of unique numbers in numerical order. I have two solutions working, one with SUMPRODUCT formulas and one with MACRO. Here's the MACRO solution. But it takes about 20 seconds to execute. I am just looking to cut down on execution time. I am using this macro a few times on different lists in the same program. Is there a better [faster] way to do this?
I'm trying to compare values in 2 separate columns to see how many times the same value appears in both columns. Ideally I would be able to insert a range function to compare the values in the column "ID 1" against the values in column "ID 2" and return the count of times that a value appears in both columns. For example 2122, 1112 and 1718 appear in both columns and I would like the formula to return a count of 3.
ID 1ID 2
12342122
45671112
89101718
11122678
13144544
15162324
17189987
19201215
21221928
1976
2576
2345
4678
In my actual project I'm comparing 2 columns in the same worksheet. The column are column B with data in cells B2:B10266 against column C with data in cells C2:C18560.
what I'm after is a macro to check the contents of Column 'A' against column 'B' and display any duplicates in Columns 'C' & 'D'.
N.B. The headings of Columns C & D are :-
C = Value Found in Column A
D = Value Found in Column B
Any duplicate entries logged in columns C & D should be listed in C2,C3,C4....C20 and D2,D3,D4......D20 etc (in effect creating two new lists)
I would like to take the data from worksheet1 and put into worksheet2 but limit the length of a list (the real spread sheet has over 100 rows and i would like them in 4 sets of 25 versus the example I provided). Is there an array or macro that would make this work (keeping the formatting)..
Excel 2007
B
C
3
Name
Company
[Code].....
2014-07-30_16-52-23.png
Fist I want to compare ColB=ColH, if TRUE, compare colA=colG, if TRUE, again compare colC=colI, IF all this conditions true, then give (colD-colJ) on colL.
all the unmatched rows in 2 tables to populate with different for each table
I need to compare add Column A&B in sheet1 and column A&B in sheet two and compare the two result for the difference(like vlookup).
formula to merge two cells in two sheet and compare.
i've check the other threads on how to compare two columns but for some reason it is still not working for me.
I have 2 columns. Column A is the master column and column B is the comparing list. If the company that is listed in Column B (Anywhere in column B) matches exactly with the company name in column A, I would like it to say "check" in column C.
I have two diffrent sets of columns of data on a spreadsheet one column set is A,B the other is D,E I want a formula or makro that will check columns A,B and compare it to D,E and if it finds a match will paste it in G,H is that possible and how would I do this?
View 9 Replies View RelatedI have two worksheets but have a same column named "Admin No.". I will need to compare the 2 "admin no." column.
Example:
Worksheet 1
Admin No.Name
1111Ali
2222James
3333Janet
4444Lihua
5555Ting Ting
6666Jasmine
Worksheet 2
Admin No.Name
1100Alvin
2200John
3300Mavis
4411Haoyi
5555Ting Ting
6677Jason
I will ike to compare both "admin no." column and when worksheet 1 5555 can be find in worksheet 2, it will return True. 6666 cannot find in worksheet 2 then it will return false.
I have numeric value in columns B, D, F, H.
I want to compare the values of these four cells in each row and update columns M (with the minimum value) and N (with the column of minimum value).
How can I manage this comparison?
I need to compare two columns A and B with a result in column E. BUT I don't want the result to be added together. ( since it's receipt numbers ) SO my formula is incorrect I need a formula that will show the receipt numbers next to each other divided by a comma or forward slash...
View 7 Replies View RelatedI have column A with 228 rows and column D with 314 rows. Both columns have the same data except that D has different data. I would like to line up everything that is the same in A and D and everything else in column D that is not the same move to G. Is there a quick way to do this?
View 5 Replies View RelatedI can find lots of ways to compare two lists with single columns, but I need a very basic guide to comparing multiple ones across two sets of data and then highlighting or extracting the unique records. I would prefer to keep the data in the existing columns for later sorting and other purposes.
I currently have the two datasets as two separate worksheets in an Excel 2003 file. The two datasets consist of the same three columns containing strings of text, but there is quite a difference in the number of rows. Dataset One is c. 3550 records, Dataset 2 is c. 1600. There are no duplicate records within each dataset, but there are duplications across the datasets and the records are in a different order, so it's not a matter of comparing Row 1 to Row 1 and so on. The data look a bit like this (semi-colons to show columns):
DATASET 1
Group;Family;Name
PM;Smith;Mary Anne
PM;Jones;Fred Henry
PD;Wilson;Peter John
PG;Green;William Laurence
...............
I need a Vlookup to look at two columns when searching. here is an example:
Sheet 1 is my report grid to hand out to the employees, A1 has a drop-down list with my employees names and A2 one for the month, when you click an employee name the vlookup formula fills in the information from sheet 2 (data sheet).
Sheet 2 has data like: Col 1= Month, col 2=employee L-name, col 3= production etc...
currently the vlookup formula messes up because the employee name is in there multiple times because of the months column, i have to delete all months except the one i need. formula is: =vlookup(A1,'sheet2'!,B:C,2,false)
this formula will pull the data in sheet two column 3 (production) for the employee that matches.
how can i make it compare first to col 1 (month) and then col 2 (employee).
I want to compare Columns A and B and Insert value in C.
So if Column A is null, then place value of column B in C, otherwise place Value of A in C.
I want to compare two Excel workbooks which have multiple columns and identify the rows only if all the valves match in both the workbooks, i am attaching a file to understand better.
View 3 Replies View RelatedI have two columns that each contain about 100 serial numbers. Most of the serial numbers shoud be in both columns. How to I determine if there is a serial number that is only in one of the columns and not in the other?
View 4 Replies View RelatedI want to compare many columns and find duplication in the last one;
Example:
Column A Column B Column C Column D Column E
1234 1234 1 A OK
1235 1235 1 A OK
1236 1236 3 A OK
1237 1236 0 A OK
1238 1236 1 B Duplication found in different column
1239 1238 0 A OK
To get Column "C" which is calculate how many numbers in that row are repeated with respect to Column "B", I solve it by using : =COUNTIF(B:B,A2) ;
But I want to get a result of duplication in "Column E" by see if "Column B" + "Column D" has the same value regardless of number then say "OK", but if it is not than the before, then write "Duplication found in different column".
The code below compares values on sheet 4 column A to sheet 3 column A and then colors a cell "Green" (Temporary), later I will place data from sheet 4 into sheet 3.
I dont know why but when it finds data on sheet 4 that is not 100% numeric it errors out.
Run-time error '91'
Object variable or With block variable not set.
The data in sheet 4 column A is primarily numeric, there are and always will be some numeric/alpha strings.
I can change the value of sheet 4 A2 to "123x" from "123" and the code stops as described. Leaving sheet 4 A1 as 100% numeric, which works fine.
i'm trying to do some analyzing on an excel sheet and was seeing if there was a function that I could use to speed this up.
So in the file...
I want to Group what's in Column A So the 1's together and the 2's together... Once that is selected I want to know what the largest number in Column C is...
So if the function can select 1 in column A then tell me that 10 is the biggest number in Column C....
I'm trying to compare the 1st 7 days average this year vs the 1st 7 days average last year. How do I write the formula?
Last YearThis Year
536,212
852,865 417,290
345,981 702,816
850,560 189,808
335,285 691,152
572,165 283,036
842,145 404,916
735,364 528,332
121,475
348,793
697,944
876,360
671,170
928,103
I would like to have a macro or a function, which could compare two "A" columns in different workbooks. macro should run from the first one wb, which gonna have less data in a column than wb2. so, if, let's say, A3 in wb1 differs from A3 in wb2, an empty row should be added in wb1, and now we would jump and compare A4 in wb1 with A4 in wb2 and so on..
View 2 Replies View RelatedI have problem comparing dates in two columns. In these columns, with dates, there can be instead of date letter "X". When I try to compare dates I don't get good results. I have to see that date in column P has been before date in column Q (example 22.02.2012 (P), 23.02.2012(Q), OK).
Code:
Sub test()
For Each oneCell In Range("Q2:Q60001")
Select Case oneCell.Value
Case Is "X"
If oneCell.Offset(0, -1) "X" Then
[Code] ........