This should be doable via VBA. How many columns are there in each of your data sets? What do you wish to do with the rows which haven't a match in the opposite data set?
This is a discussion on Aligning two merged spreadsheets within the Excel Questions forums, part of the Question Forums category; I am trying to align two merged spreadsheets. I sorted both by the columns that are in common before I ...
I am trying to align two merged spreadsheets. I sorted both by the columns that are in common before I merged them and placed the columns at the end on the spreadsheet and at the beginning on the spreadsheet I pasted to the first spreadsheet so that those columns would be next to each other. I was then trying to align the rows from spreadsheet 1 and 2 according to the like column. I am doing this by doing a shift cell up or down to align the rows. The problem is that my combined spreadsheet is 4,000 rows in length and it takes me about 4 hours to go line by line aligning the rows from spreadsheet 1 (to the left of the combined spreadsheet) and spreadsheet 2 (to the right of the combined spreadsheet). The like columns are not quite exact. The first spreadsheet has the common data entered as "########0J" and the second spreadsheet has the common data entered as "0J########". The pound signs being a series of numbers. The series of numbers are alike however. Example "02940910J" and "0J02940910". As you can see, 0294091 is alike in both the columns. Can Excel align the columns of the two merged databases for me based on the two columns containing the like data rather than me having to manually do it?
There are 13 columns from the first database and 21 columns on the second database. Column 13 is has the like data from the first database and column 14 has the like data from the second database. I would like to align all the data from columns 1-13 with the columns 14-21 where the data is the same in columns 13 and 14, keeping in mind that the data is not exactly the same in columns 13 and 14.
Just to make sure I understand: You want to--Originally Posted by cmartinez
1] end up with an x row by 21 column sheet,
2] where columns 1-13 are from database-1, and
3] columns 14-21 are the "matching" (as originally described) columns from database-2, and
4] do *not* want the data from cols 1-13 from database-2 kept, and
5] want to do ?what? if a row from database-1 has no "match" in database-2 (or is this not possible)?
Basically, what I have is one combined spreadsheet where the data in columns 1-13 are from spreadsheet #1. The data in columns 14-21 are from spreadsheet #2. Example as follows:
Columns 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21
Row #1 # # # # # # # # # # # # A A # # # # # # #
Row #2 # # # # # # # # # # # # B C # # # # # # #
Row #3 # # # # # # # # # # # # C D # # # # # # #
Notice row #1 matches in columns 13 and 14, therefore, that row is okay. Row #2 does not match as "B" is in column 13 and "C" is in column 14 rather than "C" being in both columns, therefore I have to do an insert cell shift down for columns 14-21 of row #2 in order for them to align with columns #1-13 of row #3 so columns 13 and 14 will then be aligned and columns 13 and 14 will both have the value of "C". I am currently having to manually align the data in columns 1-13 with the data in columns 14-21 row by row where columns 13 and 14 do not match.
The second problem is that the data does not exactly match in columns 13 and 14. The middle 6 characters do match but the first and last two characters do not match. Therefore, a condition such as align the data in columns 1-13 with the data in columns 14-21 where the data in column 13 and 14 match may not work as it is only the middle 6 characters of the data in columns 13 and 14 that match. Can the characters in columns 13 and 14 be masked so that excel is only looking at the center 6 characters of columns 13 and 14 to match?
Yeah, what I'm thinking of doing is to write a small macro which would:
1] Grab columns 1-13 and write them to a new sheet, then
2] assign the middle 6 characters from col 13 to a variable, then
3] go down column 14, searching for a mid-6 to mid-6 match, which
4] when found would bring over to the new sheet cols 14-21;
5] grab row #2, and so on...
It should not be hard to do, however I'm getting ready to close for a while. Tell you what -- if you've not got a solution by say noon CST tomorrow, send me an email or PM. Writing the thing shouldn't take more than 10 minutes; a few more to double-check it. Sound good to you?
Sounds good to me. If I can get formulas to do what takes me 4+ hours to do, that would be great! I have to constantly run this thing as the data from both spreadsheets changes daily as the data for columns 1-13 and 14-21 are extracted from databases that are updated daily. Thanks.
Just peeked in... please send me m email/PM to remind me to do this tomorow. Really should not take long. If you want to, include a sinlge row giving a sample of a match between the two, but from what you have already said I believe I have enough information to finish the task. (Unless, of course, the middle 6 caharacters of the one column matxh *more* than 1 instance in the other. Even then, there should be a workaround.)
Limited testing only, but this should do:
If this doesn't do the trick, get back to us and we'll fix it.Code:Sub ComeTogetherRightNowOverMe() Dim i As Long, j As Integer, k As Long, TgtStr As String Dim FirstRow As Integer, LastRow As Long, si, so Dim MatchCount As Integer, NoMatchCount As Integer Set si = ThisWorkbook.Worksheets("Sheet1") ' **** Change this sheetname to your current 21 column sheet name Set so = ThisWorkbook.Worksheets("Sheet2") ' **** Change this sheetname to the sheet name you want for the sorted sheet FirstRow = 1 ' **** Change the 1 to whatever the 1st data row is, if you have header rows LastRow = si.Range("A65535").End(xlUp).Row so.Cells.ClearContents For i = FirstRow To LastRow TgtStr = Left(si.Cells(i, 13), 7) si.Range(si.Cells(i, 1), si.Cells(i, 13)).Copy Destination:=so.Range(so.Cells(i, 1), so.Cells(i, 13)) For k = FirstRow To LastRow If (TgtStr = Right(si.Cells(k, 14), 7)) Then ' We have a match here... si.Range(si.Cells(k, 14), si.Cells(k, 21)).Copy Destination:=so.Range(so.Cells(i, 14), so.Cells(i, 21)) MatchCount = MatchCount + 1 Exit For End If If (k = LastRow) Then NoMatchCount = NoMatchCount + 1 Next k Next i MsgBox "There were " & MatchCount & "matches; there were " & NoMatchCount & " records without a match." End Sub
EDIT: Each time this runs it is set to over-write the output sheet. In addition, instead of a mid-6 match, I matched the left-most 7 characters from column 13 with the right-most 7 characters from column 14, which is what your original post suggested.
FCDP 00926140J 0J00926140 MU John
FG8F 00926220J 0J00926220 LGR Mary
FG8F 00926240J 0J00926220 LGR David
FG8F 00926360J 0J00926240 LGRDM Richard
FG8F 00926450J 0J00926360 LGRDMI Nancy
Here is an example of data on my spreadsheet. I did not include all 21 columns so it would be easier to see on this limited space. Column 2 and 3 are my columns 13 and 14 (containing the data I am aligning). As you can see, row 1 and 2 are aligned okay. Row 3, column 2, however, is 00926240J and row 3, column 3 is 0J00926220. As you can see, they do not match. What I am currently doing is grabbing row 3 from columns 1-13 and doing an insert, cell, shift cell down so that then those columns from that row will move downward and align with column 4-6 (my columns 14-21). It will then look like this:
FCDP 00926140J 0J00926140 MU John
FG8F 00926220J 0J00926220 LGR Mary
0J00926220 LGR David
FG8F 00926240J 0J00926240 LGRDM Richard
FG8F 00926360J 0J00926360 LGRDMI Nancy
The rows are aligned according to columns 2 and 3 (my 13 and 14). Just to get a better understanding, columns 1-13 contain data from a database of employee information. Column 2 (my column 13) list employee position numbers along with other data associated with that employee position number from that same database (in my columns 1-12). Column 3 (my column 14) list employee position numbers (in a different format) along with other data associated with that employee position number from the second database (in my columns 15-21). As you can see, I am aligning the rows base on columns 2 and 3 (my columns 13 and 14). At times, the data in column 3 (my column 14) are the same such as above where 0J00926220 is listed twice for both Mary and David. The number is never repeated on column 2 (my column 13) just in column 3 (my column 14). This means that both Mary and David have the same position number in my second database. When you reply with the instructions, can you please be very detailed as I am a novice with Excel. Thanks.