Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Stumped....Matching Columns
Hey, I have no idea how to do this: I have two columns with almost the exact same numbers. What I need to do is match the columns together so that the numbers that are similar match each other...for example the list would look like this: 2066078 2066078 2066079 2066078 2066080 2066078 2066081 2066078 2066082 2066079 2066083 2066080 2066084 2066081 2066085 2066082 2066086 2066083 and it should look like this: 2066078 2066078 2066078 2066079 2066079 2066080 2066080 2066081 2066081 2066082 2066082 2066083 2066083 Any ideas? -- c0nfusEd :{ ------------------------------------------------------------------------ c0nfusEd :{'s Profile: http://www.excelforum.com/member.php...o&userid=36327 View this thread: http://www.excelforum.com/showthread...hreadid=561103 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Stumped....Matching Columns
Assuming these are columns A and B, how about this? 1) Sort both columns into numerical order 2) Insert/Name/Define and give the second column a name, e.g. Range1 3) In a column C column enter this formula =IF(LOOKUP(A1,range1)=A1,A1,"") Then copy the formula down column for as many rows as there are in column A -- Special-K ------------------------------------------------------------------------ Special-K's Profile: http://www.excelforum.com/member.php...fo&userid=7470 View this thread: http://www.excelforum.com/showthread...hreadid=561103 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Stumped....Matching Columns
thanks for the response special K, but unfortunately thats not what im looking to do. I guess I wasnt clear. I have two sets of data merged into one spreadsheet, but with similar ID#, I am trying to match the ID# together so if this is what is in my columns: F01B 31/14 2066196 2066196 123353 C12N 15/16 2066197 2066196 1051593 C03C 17/34 2066198 2066197 124452 D01F 9/12 2066199 2066198 124454 D04H 1/70 2066200 2066199 123449 G07D 7/00 2066201 2066200 990031 G11B 7/00 2066202 2066201 124455 C07D 209/48 2066203 2066202 1160521 C12N 15/12 2066204 2066203 124456 C07C 2/66 2066205 2066203 1052512 I somehow want to match the 2 columns so that they look like this: F01B 31/14 2066196 2066196 123353 2066196 1051593 C12N 15/16 2066197 2066197 124452 C03C 17/34 2066198 2066198 124454 D01F 9/12 2066199 2066199 123449 D04H 1/70 2066200 2066200 990031 G07D 7/00 2066201 2066201 124455 G11B 7/00 2066202 2066202 1160521 C07D 209/48 2066203 2066203 124456 2066203 1052512 C12N 15/12 2066204 2066204 103124 Hopefully this is a little more clear. I have 440,000 cells of this stuff (like 13 spreadsheets) so if anyone could help me with this, your help would be much aprreciated! As it would take me a month to do it the way im doing it now. Thanks. -- c0nfusEd :{ ------------------------------------------------------------------------ c0nfusEd :{'s Profile: http://www.excelforum.com/member.php...o&userid=36327 View this thread: http://www.excelforum.com/showthread...hreadid=561103 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Stumped....Matching Columns
Give this try (only limited testing):
Sub transform() Dim ws1 As Worksheet Set ws1 = Worksheets("Sheet1") With ws1 lastrow = .Cells(Rows.Count, "A").End(xlUp).Row For r = 2 To lastrow If .Cells(r, "B") < .Cells(r, "C") Then .Cells(r, "A").Resize(1, 2).Insert Shift:=xlDown .Cells(r, "c").Resize(1, 2).Cut .Cells(r, "b") End If Next r End With End Sub HTH "c0nfusEd :{" wrote: thanks for the response special K, but unfortunately thats not what im looking to do. I guess I wasnt clear. I have two sets of data merged into one spreadsheet, but with similar ID#, I am trying to match the ID# together so if this is what is in my columns: F01B 31/14 2066196 2066196 123353 C12N 15/16 2066197 2066196 1051593 C03C 17/34 2066198 2066197 124452 D01F 9/12 2066199 2066198 124454 D04H 1/70 2066200 2066199 123449 G07D 7/00 2066201 2066200 990031 G11B 7/00 2066202 2066201 124455 C07D 209/48 2066203 2066202 1160521 C12N 15/12 2066204 2066203 124456 C07C 2/66 2066205 2066203 1052512 I somehow want to match the 2 columns so that they look like this: F01B 31/14 2066196 2066196 123353 2066196 1051593 C12N 15/16 2066197 2066197 124452 C03C 17/34 2066198 2066198 124454 D01F 9/12 2066199 2066199 123449 D04H 1/70 2066200 2066200 990031 G07D 7/00 2066201 2066201 124455 G11B 7/00 2066202 2066202 1160521 C07D 209/48 2066203 2066203 124456 2066203 1052512 C12N 15/12 2066204 2066204 103124 Hopefully this is a little more clear. I have 440,000 cells of this stuff (like 13 spreadsheets) so if anyone could help me with this, your help would be much aprreciated! As it would take me a month to do it the way im doing it now. Thanks. -- c0nfusEd :{ ------------------------------------------------------------------------ c0nfusEd :{'s Profile: http://www.excelforum.com/member.php...o&userid=36327 View this thread: http://www.excelforum.com/showthread...hreadid=561103 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I wrap Excel columns in a printout? | Excel Discussion (Misc queries) | |||
Finding Matching Symbols in 2 Columns | Excel Worksheet Functions | |||
Need formula that Counts items matching criteria using two columns | Excel Worksheet Functions | |||
Filtering Columns to Align Matching Data | Excel Worksheet Functions | |||
Removing Near-Duplicate Rows, Leaving Those w/Most Data in Specific Columns | Excel Discussion (Misc queries) |