Matching Pairs Of Data
Dec 30, 2008
I'm not sure if it's possible to do this, but I have three lists of data. One is a complete list (for example, the numbers 1-25).
The next list is a subset of the complete list (e.g., 1,3,5,7,9). Attached to these (the subset list) is another list (let's say letters, so A goes with 1, B goes with 3, etc). I want to physically move the paired entries from Lists 2 & 3 so that List 2 matches up with List
1. Let's see if I can represent this visually:
I have:
1|1|A
2|3|B
3|5|C
4|7|D
5|9|E
6|
7|
8|
...
25|
I want:
1|1|A
2|
3|3|B
4|
5|5|C
6|
7|7|D
8|
...
25|
View 3 Replies
ADVERTISEMENT
Aug 13, 2014
I've got 3 pairs of columns and I need to sort through them and align the cells in columns E&F with those in A&B and C&D. The cells I need to match up are the times (columns A, C and E)
Example - convert this:
A...............................B..........C...............................D.........E...............................F......
BID TIME.....................BID.......ASK TIME....................ASK......TRADE TIME................TRADE
30/07/2014 14:21:04.....6.10.....30/07/2014 14:22:37.....6.13.....30/07/2014 14:21:04.....6.13
30/07/2014 14:21:06.....6.11.....30/07/2014 14:22:54.....6.13.....30/07/2014 14:22:37.....6.13
30/07/2014 14:22:37.....6.11.....30/07/2014 14:22:56.....6.13.....30/07/2014 14:22:54.....6.13
30/07/2014 14:22:54.....6.11.....30/07/2014 14:22:56.....6.14.....30/07/2014 14:22:56.....6.13
30/07/2014 14:22:56.....6.11.....30/07/2014 14:22:59.....6.13.....30/07/2014 14:22:59.....6.13
Into this:
BID TIME.....................BID.......ASK TIME....................ASK......TRADE TIME................TRADE
30/07/2014 14:21:04.....6.10.................................................30/07/2014 14:21:04.....6.13
30/07/2014 14:21:06.....6.11........................................................................................
30/07/2014 14:22:37.....6.11.....30/07/2014 14:22:37.....6.13.....30/07/2014 14:22:37.....6.13
30/07/2014 14:22:54.....6.11.....30/07/2014 14:22:54.....6.13.....30/07/2014 14:22:54.....6.13
30/07/2014 14:22:56.....6.11.....30/07/2014 14:22:56.....6.13.....30/07/2014 14:22:56.....6.13
............................................30/07/2014 14:22:56.....6.14............................................
............................................30/07/2014 14:22:59.....6.13.....30/07/2014 14:22:59.....6.13
I don't know VBA so hopefully there's a way of doing this with a basic Excel function.
View 2 Replies
View Related
Sep 13, 2013
I have a long master list of registered members, column C has last name, column D has join date.
Now I have a short list of last names with join dates.
I want to compare the short list with the master list to find names that are already there, by comparing the last name and join date.
View 8 Replies
View Related
Nov 9, 2008
I have a database with 6 columns in play (there are actually other columns but they are not relevant). I'll call the columns A through F. I would like to be able to match certain counterpart rows together, do a sort placing the counterpart rows adjacent to one another, and then count how many pairs I have. (Some rows will have no counterparts.)
Here is a micro-illustration of the database:
______A______B______C______D________E_____F
R1___01-03___54____959____nsneakr___24____yes
R2___01-04___67____454____adidaht____53____yes
R3___01-10___42____344____calb3wd___11____no
R4___01-19___67____454____adidaht____53____no
R5___01-25___54____959____nsneakr___24____yes
R6___02-02___54____959____nsneakr___24____no
R7___02-14___54____959____nsneakr___24____no
I basically need to devise a formula or script that pairs together two rows that fit the following criteria:
1) The rows are identical in Columns B, C, D, and E.
2) The rows are not identical in Column F (i.e., one half of the pair should have "yes" and the other half should have "no")
3) The rows are as close together as possible according to the date sequence in Column A. For example, Row 1 should pair with Row 6, and Row 5 should pair with Row 7. Row 1 should not pair with Row 7, and Row 5 should not pair with Row 6. **This criterion seems tricky because R5 and R6 would technically fit the requirement for pairing, were it not for the fact that R1 comes earlier in the sequence.**
View 2 Replies
View Related
Mar 12, 2013
This is what I need:
Columns B, C, D & E are all populated with 3 digit numbers.
I would like column F to automatically populate with any of the 3 digit numbers that share two numbers, i.e.
F2 might look like this (using 00 as the pair):
001, 040
F3 might look like this (using 01 as the pair):
701, 051, 110, 001, 120
F4 might look like this (using 12 as the pair):
123, 721, 281, 912, 112, 120
etc...
View 1 Replies
View Related
Jun 9, 2014
I want to store a list of strings in an array with their position so that I can sort them alphabetically and then be able to put them back in their original order again e.g.
John 1
Charlie 2
Paul 3
Andy 4
etc,
So when they are alphabetized (is that a word?) they become
Andy 4
Charlie 2
John 1
Paul 3
My plan was to store them in an array, but I can't think how to do it. In python, I would just make a list of tuples, what is the best way to do this in VBA?
View 7 Replies
View Related
Oct 4, 2013
I need to complete what would seem a fairly simple incrementing task but I'm not getting the results that I'm looking for. I don't know much about macros so would prefer not having to use one but I'm willing to try if it's not too complicated.
I'm using Excel 2010 on a PC. For several projects, I need to increment cells that contain text and numbers. The column contains data in a cell (A1), followed by a blank cell (A2), then cell A3 contains new data followed by a blank in A4, etc. What I need to do is 1) copy the exact data in A1 to A2 and then 2) increment the number used in A1/A2 by 1 in A3, copy that into A4, and the pattern repeats down the column.
Here is an example of what I have in column A:
Test_01
[blank cell]
Test_02
[blank cell]
Test_03
[blank cell]
etc.
This is what I want as a result.
Test_01
Test_01
Test_02
Test_02
Test_03
Test_03
etc.
Do I need to save the data in the column as text, general, or numbers? Is there some function that would make incrementing the data possible as I've outlined above?
View 8 Replies
View Related
Oct 22, 2011
Is there any way to find pairs of numbers from lotto data ,starting from the highest to the lowest for the full 24 pairs? It does not matter if excel formula or Vb code.T
here small example;
DATARESULT20 27 28 34 43 44PairsCount13 14 18 32 34 4927, 34207 12 27 34 41 465, 47213 24 25 26 37 40 29, 38206 16 22 26 30 3426,37209 15 17 29 30 3915,33104 15 17 26 37 419,22115 19 25 36 44 4510 16 29 34 37 4711 15 32 33 37 3902 03 06 41 44 45 23 28 29 37 38 4205 07 09 10 22 4704 29 31 36 38 4804 17 20 26 29 3605 26 43 46 47 4906 07 29 32 41 46 11 27 46 47 48 4902 03 18 30 34 3503 19 32 40 47 4805 30 31 33 35 45
View 9 Replies
View Related
Jan 20, 2013
My task is to combine two large databases into one spreadsheet by extending the number of columns. The data from each database is 90% matching based on an identification number, however occasionally there are additional rows or unmatched identification numbers that need to be kept for analysis.
When this happens, there needs to be a blank row inserted to represent the missing data in the rest of the corresponding row.
I am having trouble finding a quick way to do this because I have approximately 12,000 rows (and columns up to DV when combined).
for example:
p1
data
data
data
data
p2
data
data
data
data
[Code] .....
needs to become:
p1
data
data
data
data
[Code] .....
I am guessing I will need a macro of sorts, So far I have made one column that tell me if the ID's are matching or not (1 or 0) and if they are not matching (0) I manually insert the rest of the row that is missing or make space for the duplicate data (which needs to remain).
View 5 Replies
View Related
Feb 27, 2014
I have two tables, Table1 one has only customer codes in it, and I have Table2 with plenty of customer codes and those customer name, surname, age, location...
And I want to match and copy each of those customer codes in Table 1 all the information which is on Table2
Table 1:
50025
50026
50086
Table 2: Considering that name, age and location is each in separate cell
50025JohnSecond25Location 1
60085EmilyThird 20Location 2
45454Wilhelm Fourth35Location 3
10000RoseFifth 60Location 4
50086JohnySixth 45Location 5
65501JacobSeventh18Location 6
50026Jackie Eighth22Location 7
And the outcame should be that it finds the value from Table1 in Table2 and copy's information in Table 1 like this:
50025 JohnSecond25Location 1
50026 Jackie Eighth22Location 7
50086 Johny Sixth 45Location 5
View 2 Replies
View Related
Feb 20, 2014
I have two worksheet "ABX" and "ACX" from which I want to find the matching data for specific columns B, D and E only.
Matching data will be in sheet "Match" and vice versa.
Data are present dynamically.
View 1 Replies
View Related
Apr 20, 2009
I'm looking for an easy way to fill-in 1600 cells, in Col. "A", with pairs of sequential numbers [from 1 to 800] - for example:
1
1
2
2
3
3
4
4
5
5
6
6
I managed to achieve something close with: =INT(ROW()/2.05)+1 but this is not accurate enough.
View 2 Replies
View Related
Jun 8, 2008
I would like to know if its possible to extract just the even/odd pairs from a number.
Exp. I have the number 123 in cell A1, and would like to extract the even/odd pair, which in this case would be 13, the cell A2. I would like to be able to do this without the use of "Isodd" or "iseven" functions.
View 9 Replies
View Related
Mar 16, 2009
Need formula? We have a pair of numbers in D1,E1,and F1…..
We want only the numbers that have 12 from column A and B
66 from column A and B
18 from column A and B only
A B C D E F
1
12 66 18
456 123
129 667 481
667 234
123 366 881 .........
View 9 Replies
View Related
Jul 19, 2006
I am looking for a code which will check and display the Maximum Occurrences of pairs of 2 Characters.
In the attached file I tried to write some code but it seems to be wrong.
For your convinience, I entered the reuslts in a table (hopefully not mistaken).
The code should display a MsgBox saying that "The pair 'ab' was found 6 times in A1.
The string, in A1, can be any string in any length.
View 9 Replies
View Related
Oct 20, 2007
I am trying to analyse a set of variables in my dataset.
The variables I have are numbers which are organised into a single column.
I want to count the number of times different variable pairings occur within my data.
An example dataset of mine is: 1,2,4,2,1,3,5,3,2,1
As the dataset contains 5 variables (1,2,3,4,5) the potential combinations are: 1,1; 1,2; 1,3; 2,1; etc.. with 25 in total.
I want Excel to move through this column starting with the first two cells and step down 1 cell each time and then tell me the number of times each of the combination occurs.
View 3 Replies
View Related
Jun 22, 2014
I am trying to figure out a function that creates currency pairs, for example EUR/USD etc based on a limitation.
I attach you a workbook with the details. The first step I have accomplished, I am having trouble to the second step as you will see in the sheet.
Test.xlsx
View 8 Replies
View Related
Aug 2, 2008
I am tring to list and count how many pairs in a 4 digit lottery?
no duplicates ...
View 13 Replies
View Related
Feb 4, 2010
I have a map with several waypoints and would like to find the distance between any of these two waypoints. Sounds simple, but I have having an issue with the later parts of the excel file. Let me explain further.
There are over 100 waypoints on this map we have. Each waypoint has an, seemingly arbitrary, X-coordinate and Y-Coordinate. However, if you divide the coordinates by [X], they turn into miles. It should go without saying that finding the distance between two waypoints is rather simple mathematically, but I having an issue figuring out how to pull two random waypoints and having the distance automatically calculated.
So far, I just have column A with the waypoint name and with column B and C having the X and Y coordinates respectively. Is there anyway I can create some sort of call function that when two waypoint values are entered it would automatically calculate the distances?
View 4 Replies
View Related
Aug 14, 2007
How do I express the following as a formula for range f2:h2:
case 1
if any number is equal to 0 or 5, then 1
if any number is equal to 1 or 6, then 2
if any number is equal to 2 or 7, then 3
if any number is equal to 3 or 8, then 4
if any number is equal to 4 or 9, then 5
example:
f2:h2=3,5,9
result
415
f2:h2=0,2,7
result
133
View 9 Replies
View Related
Aug 14, 2008
I need to pair up 20 people in sequence for a workshop, so I number the people and can rotate people numbered 1-10 with 11-20 easy enough, but when it gets to pairing 1-10 together in pairs and 11-20 in pairs.
Is there a formula I could use in Excel to work out the easiest pairing for me?
View 9 Replies
View Related
Aug 8, 2006
I have a string of n pairs and want to check various combination of that string.
Example: Pairs 58 78 15
Since I know I have 3 pairs (but it can be 2 or 4), I know the number of combination I want to test, ie 2 power 3 = 8 combinations. How can I program a code creating the various strings, ie 587815, 587851, 588715, 588751, 857815, 857851, 858715, 858751 ?
This is what I have so far (not much):
Public unique_pair 'number of pairs provided by another macro
Public mystring 'provided by another macro
Sub make_guess()
Dim number_of_combination, i
number_of_combination = 0
number_of_combination = 2 ^ unique_pair
For k = 1 To number_of_combination
'how to generate the various string ????
Next k
End Sub
View 9 Replies
View Related
Jun 24, 2014
I have a spreadsheet with 14, or maybe 16, or maybe 40 names in column B.
A1 = 1 B1 = JOE CITIZEN
A2 = 2 B2 = JANE PERSON
...
A17 = 3 B17 = JACK DOE
and so on...
I want to randomly assign these names to pairs using a macro button, and basing it on the number of players (C1)
So if C1 is 12, I'd like the function behind the macro button to come up with 6 * 2 numbers (2 and 11, or 4 and 7,
all completely random, but within the 12 specified in C1) and write these random values in Range D1:E6
(if C1 is 18, then the range to write in would expand to D1:E9).
I don't know enough about writing code to be able to pull this off .
View 2 Replies
View Related
Nov 14, 2013
I have created a table that has working hours of staff members over many weeks. Week number as column headings (1 to 52) and staff name as Row headings. E.g a row may be
John Smith, 37, 37, 37, 37, 64 (commas to show seperate cells)
How would I go about using conditional formatting so that the formatting changes according to the sum of the values in each pair of cells?
I need to add the total hours of every two weeks for some staff and change the fill colour of both cells accordingly to highlight which weeks staff have worked too many/few hours.
So (B1+C1) would be a pair, the total would decide which fill colour is used on both B1 and C1, and then (D1+E1) would be the next pair and so on.
I have tried using 'a formula to determine which cells to format' and placing =(B1 + C1) = 74 and making it fill the cells green but this appears to be doing (B1+C1) as the first pair and then (C1+D1) as the second and changing the format for the first cell only.
View 7 Replies
View Related
Jan 26, 2009
I've managed to get my macro to work ok - but what I want to do now is to get it to loop through more columns. Currently it looks for email addresses in column AH (which will always be the same) then it looks for a "yes" in columnAB and if there is no "SENT" in column T then it sends an email,after which it adds the word "SENT" to column T. What I want t do is for the macro to then run on columns AC+U, AD+V, AE+W and AF+X. So basically it will loop through one column to the right each time for a total of 5 pairs of columns
View 2 Replies
View Related
Mar 13, 2009
So I have a spreadsheet that has a Title in Cell A1, then entries in B1, D1, F1, H1, J1, etc... with empty cells between.
What I would like to do is copy those entries to the right, i.e. B1 into C1, D1 into E1, F1 into H1, but all the way along because in my master sheet there are a lot of columns.
View 11 Replies
View Related
Dec 3, 2008
A:2007 Email Addresses
john@yahoo.com
Sam@hotmail.com
etc
etc
>6000 more email address9000 more email addresses)
I need to match up & clearly highlight the pairs (duplicates) in each coloumn.
What's the trick for this?
View 9 Replies
View Related
Apr 27, 2007
I have one hundred rows of data and within that there are some records which are the same and they will always be in pairs - identifiable by a cell with the same ref common to both records. how to strip out the records that are not part of a pair? I would like remove all of the non pairs and move them to another sheet.
View 2 Replies
View Related
May 5, 2009
Now that the calculations are working, with the press of a button, I need to be able to select a range of dates and copy all lines within the range to a seperate sheet with the desired name under the same headings they currently reside under. I have included some modified code that is being used in another spreadsheet that was created for me, but I do not pretent to understand all of it and I no longer work with the creator of the spreadsheet. How do I use a button to open the form for date selections and entering the name of the new sheet, and then use the start button on the form to begin the matching and copying to a new sheet? If there is an easier way I am all for that too.
View 14 Replies
View Related
Apr 18, 2013
So I have this problem in excel with comparing 2 columns.
Basically, I have 2 columns(a &B) that I need to compare with one another and find out the matching data. I am trying to use that matching data to enter in our reports.
Both columns might have duplicate items because I am dealing with premium numbers.
Formula to compare these 2 columns and find the matching data and extract it to a separate column. Keep in mind, i am dealing with almost 20,000 lines of data.
View 2 Replies
View Related