Line up rows to corresponding row of larger master list in excel

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am importing data files into excel where I need to line up the rows to
match a longer master list, so that multiple files can be viewed side by side
with the corresponding data on each row. Is there an action or formula that
does this? Or, is there a way I could create a macro to do it?
 
The imported data is in some workbook. In what columns starting with what
row? Where is the "Master" list? In a sheet in the same workbook? In a
different workbook? In what columns starting with what row? What
constitutes a "match"? In other words, what column or columns need to be
checked to determine a "match"? Does the "Master" list have an entry for
EVERY entry in the imported data? Every time? Otto
 
In column A, there is a list of 150 insurance companies, (each cell is a
2-letter abbreviation for a particular company). I import data from another
workbook, that is about 10 columns wide, the first column, which is now B
after pasting it also is a list of abbreviated insurance companies. However
the data I just pasted is shorter that the complete list on column A. There
are maybe 110 rows out of the complete list of 150. I manually enter rows for
columns B-K so that when I am finished, each row corresponds to the larger
list on column A. Then I cut column A and paste it onto the B column, which
now has the entire list of 150 ins co, with empty rows for ones which have no
data. I save this file. Then when I’ve finished doing this to about 35 files,
I paste them side-by side, so that if you look at a particular row, you will
see information on a particular insurance company through a particular period
of time. Is there a formula, command, or macro I can set up that would take
the imported data, and using column B, line it up to the corresponding
insurance in column A?
Thanks,
Linda
 
Linda
I will work up something (a macro) for you. The code (the macro)
wouldn't be able to work with the layout you describe. By this I mean
having the long list in Column A and the short list in subsequent columns
occupying many of the same rows.
If you must have that layout, I'll write the code to first move all of
Column B and the subsequent columns to some other location and then work
with the 2 locations. The result will be the same for you. It's just a
matter of how we get from here to there.
Am I correct that you have headers in row 1 and the data starts in row
2? Otto
 
Hi Otto,
The actual column headings are on row 8 and data starts on row 10. (I could
delete row 9 if needed.) Below is a sample of some of the data. The imported
data is columns B-J, so I select Columns B-J, then manually cut and paste
until Column B lines up with Column A. Since I've run out of Excel columns
before, the cutting and pasting for all the reports that are eventually
pasted side by side is a time-consuming and tedious task! I really appreciate
your help! Thanks! Linda

AR Charges Payments Adjustments Transfer Coll
__ ___________ ______________ _____________
56 56 416 0 -416 0
AH AH 2796 -1042.2 -1753.8 0
AJ BH 1277 -391.36 -885.64 0
BB BP 16370 -3771.48 -12598.52 0
BC BS 42171 -14521.64 -27280.36 -297.2
BD BT 2235 -414.41 -1820.59 0
BH CC 21182 -5353.49 -15828.51 0
BJ CD 9426 -1635.57 -7790.43 0
 
Linda

Your last email puts a new wrinkle in all this. Then I read
your previous messages and now I realize that you had mentioned it before.
Correct me if I'm wrong here.

You don't have a one-shot thing with the long list in Column A
and the short list in Columns B+. This is all a repeating thing with
subsequent imported data needing to be placed in subsequent columns. I
think. From what you say I gather that, for each cycle, you work that one
cycle in another file, then cut/paste that data into your main file. Is
this correct?

Assuming it is, let me make a suggestion and you tell me if this
can be fitted into your operation.

Have one file.

In that one file:

Have one sheet that holds the final data.

Have another sheet that holds the CURRENTLY imported data.

The code does all the cutting/pasting.

The code clears the imported data sheet.

Would that work for you?

A couple of questions: Do you want/need the code to copy/paste
the headings into the final sheet as well? Do you want any blank columns,
in the final sheet, between column sets of imported data? Otto
 
Linda

Here is a macro to get this thing started. Try it out on a copy
of your real file and we'll go from there. The following
assumptions/conditions are built in to this code. By this I mean that you
cannot change these things in your file without making the necessary changes
to the code or the code will no longer work properly. Just let me know what
you want changed.

There is only one file.

There is a sheet named "Final". It has the long list, headers in row 8,
data starts in row 10. Everything starts in Column A.

There is a sheet named "Imported Data". You would place the imported data
into this sheet. Header and data locations are the same as the Final sheet.

In each cycle (each time you import data and run this macro) the data is
pasted to the Final sheet in the column that is one column to the right of
the far right entry in row 10. There is no blank column between column sets
of data.

As written now, the column headers in the Imported Data sheet are NOT copied
to the Final sheet. Let me know if you want them copied/pasted.

After transferring all the data from one sheet to the other, the code clears
all the data in the Imported sheet from row 10 down.

It doesn't matter how many other sheets the file has.

In the event that a company is listed in the Imported Data sheet BUT NOT IN
THE FINAL SHEET, a message box will pop advising you of such and telling you
the company name, and that the program will continue with the rest of the
data.

Note that all these things can be changed by simply changing the
code, so don't feel like your hands are tied in any way.

These newsgroup posts can wrap the text of the code, so be sure
that you view this post in full screen. If you still have a wrapping
problem, we can exchange emails and I can send you a small file with the
code in it. Let me know. Otto

Sub TransferData()
Dim rFinalA As Range
Dim rImportA As Range
Dim i As Range
Dim DestCol As Long
Dim DestRow As Long
Application.ScreenUpdating = False
Sheets("Final").Select
Set rFinalA = Range("A10", Range("A" & Rows.Count).End(xlUp))
With Sheets("Imported Data")
Set rImportA = .Range("A10", .Range("A" & Rows.Count).End(xlUp))
End With
DestCol = Cells(10, Columns.Count).End(xlToLeft).Offset(, 1).Column
For Each i In rImportA
If Not rFinalA.Find(What:=i.Value, Lookat:=xlWhole) Is Nothing
Then
DestRow = rFinalA.Find(What:=i.Value, Lookat:=xlWhole).Row
i.Offset(, 1).Resize(, 8).Copy
Cells(DestRow, DestCol).PasteSpecial xlPasteValues
Else
MsgBox "The company " & i.Value & " could not be found in
Column A of the Final sheet." & Chr(13) & _
"The data for that company will not be
copied/pasted." & Chr(13) & _
"This program will continue with the rest of the
data.", 16, "Missing Company"
End If
Next i
With Sheets("Imported Data")
If Not IsEmpty(.Range("A10").Value) Then _
.Range("A10", .Range("A" & Rows.Count).End(xlUp).Offset(,
8)).ClearContents
End With
Application.ScreenUpdating = True
End Sub
 
Otto Moehrbach said:
Linda

Here is a macro to get this thing started. Try it out on a copy
of your real file and we'll go from there. The following
assumptions/conditions are built in to this code. By this I mean that you
cannot change these things in your file without making the necessary changes
to the code or the code will no longer work properly. Just let me know what
you want changed.

There is only one file.

There is a sheet named "Final". It has the long list, headers in row 8,
data starts in row 10. Everything starts in Column A.

There is a sheet named "Imported Data". You would place the imported data
into this sheet. Header and data locations are the same as the Final sheet.

In each cycle (each time you import data and run this macro) the data is
pasted to the Final sheet in the column that is one column to the right of
the far right entry in row 10. There is no blank column between column sets
of data.

As written now, the column headers in the Imported Data sheet are NOT copied
to the Final sheet. Let me know if you want them copied/pasted.

After transferring all the data from one sheet to the other, the code clears
all the data in the Imported sheet from row 10 down.

It doesn't matter how many other sheets the file has.

In the event that a company is listed in the Imported Data sheet BUT NOT IN
THE FINAL SHEET, a message box will pop advising you of such and telling you
the company name, and that the program will continue with the rest of the
data.

Note that all these things can be changed by simply changing the
code, so don't feel like your hands are tied in any way.

These newsgroup posts can wrap the text of the code, so be sure
that you view this post in full screen. If you still have a wrapping
problem, we can exchange emails and I can send you a small file with the
code in it. Let me know. Otto

Sub TransferData()
Dim rFinalA As Range
Dim rImportA As Range
Dim i As Range
Dim DestCol As Long
Dim DestRow As Long
Application.ScreenUpdating = False
Sheets("Final").Select
Set rFinalA = Range("A10", Range("A" & Rows.Count).End(xlUp))
With Sheets("Imported Data")
Set rImportA = .Range("A10", .Range("A" & Rows.Count).End(xlUp))
End With
DestCol = Cells(10, Columns.Count).End(xlToLeft).Offset(, 1).Column
For Each i In rImportA
If Not rFinalA.Find(What:=i.Value, Lookat:=xlWhole) Is Nothing
Then
DestRow = rFinalA.Find(What:=i.Value, Lookat:=xlWhole).Row
i.Offset(, 1).Resize(, 8).Copy
Cells(DestRow, DestCol).PasteSpecial xlPasteValues
Else
MsgBox "The company " & i.Value & " could not be found in
Column A of the Final sheet." & Chr(13) & _
"The data for that company will not be
copied/pasted." & Chr(13) & _
"This program will continue with the rest of the
data.", 16, "Missing Company"
End If
Next i
With Sheets("Imported Data")
If Not IsEmpty(.Range("A10").Value) Then _
.Range("A10", .Range("A" & Rows.Count).End(xlUp).Offset(,
8)).ClearContents
End With
Application.ScreenUpdating = True
End Sub
Click to expand...
 
Hi Otto,
I tried the macro, but am getting a syntax error at this point:
If Not rFinalA.Find(What:=i.Value, Lookat:=xlWhole) Is Nothing
I’m not sure I’ve done everything correctly—here’s what I did:
I made up a workbook with 2 sheets. The first tab is “Final†and the second
one is “Imported Data.†I pasted the long list of codes into column A of the
“final†sheet, and pasted a data file into the Imported Data sheet. I ran the
macro, with the cursor on A10 of the “Final†sheet and got the error message.
(I could send you this workbook, if it would be helpful.)
In answer to your question regarding the column headers, is there a way to
copy rows 1-10 (or 1-7 if the column headers are already on the final sheet)
so that I could keep track of which report it is?—With the # of reports
maxing out the # of columns in excel, it would be helpful to know which one
is which! – (I can always put a temporary label finished 10 columns if it
wouldn’t work out).
I’m humbled by the time you’ve put into this – many, many thanks!
Linda
 
Otto,
I was going back over your posts, and am answering a couple of questions I
missed:
"You don't have a one-shot thing with the long list in Column A
and the short list in Columns B+. This is all a repeating thing with
subsequent imported data needing to be placed in subsequent columns. I
think. From what you say I gather that, for each cycle, you work that one
cycle in another file, then cut/paste that data into your main file. Is
this correct?" Yes.
"Do you want/need the code to copy/paste the headings into the final sheet
as well?" Yes.
"Do you want any blank columns, in the final sheet, between column sets of
imported data?" No.

Thanks again. Linda
 
Otto,
In the line I was getting a Syntax Error, I inserted a dot between the xl &
whole:
If Not rFinalA.Find(What:=i.Value, Lookat:=xlWhole) Is Nothing
If Not rFinalA.Find(What:=i.Value, Lookat:=xl.Whole) Is Nothing

Now I'm getting a Syntax Error on the following line--I don't know how to
fix it:
Column A of the "Final" & Chr(13) & "The data for that company will not be
copied/pasted." & Chr(13) & _ "This program will continue with the rest of
the data.", 16, "Missing Company"
Linda
 
Linda
Send me your file. The code works for me with the dummy data that I put
in my file, so the problem should not be difficult to figure out. Also,
what version of Excel do you have?
My email address is (e-mail address removed). Remove the "nop" from this
address. Otto
 
Linda

When you say to copy rows 1-10, don't you mean 1-8? From what
you said before, row 8 has the headers and row 10 is the first data row. Or
is the layout (headers here, data there, etc.) of the Imported Data sheet
different from the Final sheet? I'll change the code to copy rows 1-8
unless I hear different from you. Otto
 
Otto,
I tried importing 4 files, and checked everything manually. The macro worked
like a charm. By the end of the week, I'll be starting on 3 large projects
that previously have taken many hours to line up each report side by side
manually. The stress involved, both from the time it takes, plus the
tediousness of the task, have at times been almost unbearable. I've
occasionally read questions & answers within the Microsoft Community before,
but this is the first time I've ever posed a question. I am absolutely
overwhelmed by the care, the amount of dedication, knowledge, & time you have
put into working on my problem. A heart filled with gratitude and thanks to
you, Otto!
Linda
Linda
 
Back
Top