Two-column Excel comparison with alignment
Solved
anthelot
Posted messages
5
Status
Member
-
anthelot Posted messages 5 Status Member -
anthelot Posted messages 5 Status Member -
Hello everyone!
Here is my first post on this forum! And I would be super grateful to the one who can help me with my problem! Indeed, I am stuck writing my dissertation because of this, which for most of you will seem like child’s play to solve ;)
So, in fact I have 2 columns of data in Excel: In A I have 26,010 data points and in B 21,502. Most of the data in B are found in A, but some are NOT (for example 1,0004239, the last data in B) and I would like to be able to align the identical data in B with respect to A in column C.
As an example:
Original:
A B
0,0001010 0,0003021
0,0001019 0,0003076
0,0001027 0,0001010
0,0001035 0,0003026
0,0001054 0,0001054
0,0001062 0,0001062
0,0000000 0,0002068
0,0000000 0,0003100
0,0002014 0,0002028
0,0002028 0,0002041
0,0002041 1,0004239
0,0002068
0,0000000
0,0003021
0,0003026
0,0003034
0,0000000
0,0000000
0,0003076
0,0003088
0,0003100
To:
A C
0,0001010 0,0001010
0,0001019
0,0001027
0,0001035
0,0001054 0,0001054
0,0001062 0,0001062
0,0000000
0,0002014
0,0002028 0,0002028
0,0002041 0,0002041
0,0002068 0,0002068
0,0000000
0,0003021 0,0003021
0,0003026 0,0003026
0,0003034
0,0000000
0,0000000
0,0003076 0,0003076
0,0003088
0,0003100 0,0003100
I’m not familiar with macros and I’ve already looked a lot to find a solved issue similar to mine, but where it didn’t work, or the results left B data that existed in A without a "matching"!
Many thanks to anyone who will take a few minutes to help
Have a good weekend everyone
Configuration: Windows Vista / Safari 532.5
Here is my first post on this forum! And I would be super grateful to the one who can help me with my problem! Indeed, I am stuck writing my dissertation because of this, which for most of you will seem like child’s play to solve ;)
So, in fact I have 2 columns of data in Excel: In A I have 26,010 data points and in B 21,502. Most of the data in B are found in A, but some are NOT (for example 1,0004239, the last data in B) and I would like to be able to align the identical data in B with respect to A in column C.
As an example:
Original:
A B
0,0001010 0,0003021
0,0001019 0,0003076
0,0001027 0,0001010
0,0001035 0,0003026
0,0001054 0,0001054
0,0001062 0,0001062
0,0000000 0,0002068
0,0000000 0,0003100
0,0002014 0,0002028
0,0002028 0,0002041
0,0002041 1,0004239
0,0002068
0,0000000
0,0003021
0,0003026
0,0003034
0,0000000
0,0000000
0,0003076
0,0003088
0,0003100
To:
A C
0,0001010 0,0001010
0,0001019
0,0001027
0,0001035
0,0001054 0,0001054
0,0001062 0,0001062
0,0000000
0,0002014
0,0002028 0,0002028
0,0002041 0,0002041
0,0002068 0,0002068
0,0000000
0,0003021 0,0003021
0,0003026 0,0003026
0,0003034
0,0000000
0,0000000
0,0003076 0,0003076
0,0003088
0,0003100 0,0003100
I’m not familiar with macros and I’ve already looked a lot to find a solved issue similar to mine, but where it didn’t work, or the results left B data that existed in A without a "matching"!
Many thanks to anyone who will take a few minutes to help
Have a good weekend everyone
Configuration: Windows Vista / Safari 532.5
6 answers
Actually, a quick idea-clearing note, no VBA needed for this (unless you absolutely need it)
I assumed you had a title row and that the data starts on row 2:
- insert a column B
- in B2: =IF(ISNA(MATCH(A2, C:C, 0)), "", A2)
- fill down by double-clicking the fill handle (black square at the bottom right of the selected B2)
- copy/paste Special as values of column B
eric
I assumed you had a title row and that the data starts on row 2:
- insert a column B
- in B2: =IF(ISNA(MATCH(A2, C:C, 0)), "", A2)
- fill down by double-clicking the fill handle (black square at the bottom right of the selected B2)
- copy/paste Special as values of column B
eric
Hello,
Are those just values in your table or are there formulas?
If there are formulas, they will need to be rounded (to 7 decimals?) to have a reliable comparison.
Can we sort A? and B?
Can we find the value of A twice in B?
eric
Are those just values in your table or are there formulas?
If there are formulas, they will need to be rounded (to 7 decimals?) to have a reliable comparison.
Can we sort A? and B?
Can we find the value of A twice in B?
eric
Hello Eric!
No, they are simply values to compare and there are indeed 7 decimals that differentiate each number
Thanks for your help
Antoine
No, they are simply values to compare and there are indeed 7 decimals that differentiate each number
Thanks for your help
Antoine
Oops, I hadn’t noticed...
Yes, I imagine we can sort the 2 columns because in each column independently of the other there is no value that repeats
Furthermore, there is at most only once the value of A in B (either it exists once in B, or it simply does not exist)
Yes, I imagine we can sort the 2 columns because in each column independently of the other there is no value that repeats
Furthermore, there is at most only once the value of A in B (either it exists once in B, or it simply does not exist)
See you soon on the forum and thank you again
Antoine