Matching data in Excel nd add rows to line up

[cat57]

Hello,

I'm new at this so please bear with me. I have a large amount of data that I need to compare and match in Excel and cannot figure out how to do it.
I need to compare the data in column A to column F, get them to line up when they match and insert a row, leaving it blank, if there is no match. Any help would be greatly appreciated! Thanks!

This is a sample of the data and the results I need are below it:

1865089	01/02/14	01/02/14	7517	SDC	1865089	01/02/14	01/02/14	7517
1869478	01/01/14	01/01/14	1113	INO	1869478	01/01/14	01/01/14	913
1873232	01/08/14	01/10/14	16162	SDC	1873536	01/04/14	01/04/14	971
1873536	01/04/14	01/04/14	971	INO	1873624	01/15/14	01/15/14	606
1873624	01/15/14	01/15/14	606	INO	1875540	01/10/14	01/10/14	1955
1875540	01/10/14	01/10/14	1955	INO	1878874	01/05/14	01/05/14	12654
1878616	01/08/14	01/10/14	2311	SDC	1879567	01/06/14	01/06/14	5648
1878874	01/05/14	01/05/14	5485	SDC
1879567	01/06/14	01/06/14	5648	SDC
What I need:
1865089	01/02/14	01/02/14	7517	SDC	1865089	01/02/14	01/02/14	7517
1869478	01/01/14	01/01/14	1113	INO	1869478	01/01/14	01/01/14	913
1873232	01/08/14	01/10/14	16162	SDC
1873536	01/04/14	01/04/14	971	INO	1873536	01/04/14	01/04/14	971
1873624	01/15/14	01/15/14	606	INO	1873624	01/15/14	01/15/14	606
1875540	01/10/14	01/10/14	1955	INO	1875540	01/10/14	01/10/14	1955
1878616	01/08/14	01/10/14	2311	SDC
1878874	01/05/14	01/05/14	5485	SDC	1878874	01/05/14	01/05/14	12654
1879567	01/06/14	01/06/14	5648	SDC	1879567	01/06/14	01/06/14	5648

<COLGROUP><COL style="WIDTH: 69pt" width=92><COL style="WIDTH: 69pt" span=2 width=92><COL style="WIDTH: 69pt" span=6 width=92><TBODY>
</TBODY>

 

[steve the fish]

A simple vlookup will do what you require. Put the first table in sheet1 and the first 'half' of the table in another sheet, columns A:E.

Then in F1:
=IFERROR(VLOOKUP($A1,Sheet1!$F:$I,1,0),"")
G1:
=IFERROR(VLOOKUP($A1,Sheet1!$F:$I,2,0),"")
H1:
=IFERROR(VLOOKUP($A1,Sheet1!$F:$I,3,0),"")
I1:
=IFERROR(VLOOKUP($A1,Sheet1!$F:$I,4,0),"")

Copy down as far as required

 

[hiker95]

cat57,

Welcome to the MrExcel forum.

1. What version of Excel and Windows are you using?

2. Are you using a PC or a Mac?


With your raw data in columns A thru E, sorted/grouped by column A, and, the data in columns F thru I, sorted/grouped by column F.


Sample raw data:

Excel 2007
	A	B	C	D	E	F	G	H	I
1	1865089	1/2/2014	1/2/2014	7517	SDC	1865089	1/2/2014	1/2/2014	7517
2	1869478	1/1/2014	1/1/2014	1113	INO	1869478	1/1/2014	1/1/2014	913
3	1873232	1/8/2014	1/10/2014	16162	SDC	1873536	1/4/2014	1/4/2014	971
4	1873536	1/4/2014	1/4/2014	971	INO	1873624	1/15/2014	1/15/2014	606
5	1873624	1/15/2014	1/15/2014	606	INO	1875540	1/10/2014	1/10/2014	1955
6	1875540	1/10/2014	1/10/2014	1955	INO	1878874	1/5/2014	1/5/2014	12654
7	1878616	1/8/2014	1/10/2014	2311	SDC	1879567	1/6/2014	1/6/2014	5648
8	1878874	1/5/2014	1/5/2014	5485	SDC
9	1879567	1/6/2014	1/6/2014	5648	SDC
10
Sheet1

After the macro:

Excel 2007
	A	B	C	D	E	F	G	H	I
1	1865089	1/2/2014	1/2/2014	7517	SDC	1865089	1/2/2014	1/2/2014	7517
2	1869478	1/1/2014	1/1/2014	1113	INO	1869478	1/1/2014	1/1/2014	913
3	1873232	1/8/2014	1/10/2014	16162	SDC
4	1873536	1/4/2014	1/4/2014	971	INO	1873536	1/4/2014	1/4/2014	971
5	1873624	1/15/2014	1/15/2014	606	INO	1873624	1/15/2014	1/15/2014	606
6	1875540	1/10/2014	1/10/2014	1955	INO	1875540	1/10/2014	1/10/2014	1955
7	1878616	1/8/2014	1/10/2014	2311	SDC
8	1878874	1/5/2014	1/5/2014	5485	SDC	1878874	1/5/2014	1/5/2014	12654
9	1879567	1/6/2014	1/6/2014	5648	SDC	1879567	1/6/2014	1/6/2014	5648
10
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sub AlignData()
' hiker95, 09/05/2014, ME803639
Dim r As Long, lr As Long, d As Range
Application.ScreenUpdating = False
lr = Cells.Find("*", , xlValues, xlWhole, xlByRows, xlPrevious, False).Row
Set d = Range("A1:A" & lr)
r = 1
Do While d.Cells(r, 1) <> ""
  If d.Cells(r, 1).Offset(, 5) <> "" Then
    If d.Cells(r, 1) < d.Cells(r, 1).Offset(, 5) Then
      d.Cells(r, 1).Offset(, 5).Resize(, 4).Insert -4121
    ElseIf d.Cells(r, 1) > d.Cells(r, 5).Offset(, 1) Then
      d.Cells(r, 1).Resize(, 5).Insert -4121
      lr = lr + 1
      Set d = Range("A1:A" & lr)
    End If
  End If
  r = r + 1
Loop
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the AlignData macro.

 

[cat57]

Hi,

I'm using a PC and Excel 2010 - will try your macro and let you know how it goes

Thanks.

 

[cat57]

Works great! Thank you so much for your help!
One last question; how do I save the macro for continued use so I don't have to keep copying and pasting? Thanks.

 

[hiker95]

cat57,

Works great! Thank you so much for your help!

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.

One last question; how do I save the macro for continued use so I don't have to keep copying and pasting?

I am not familiar with Excel 2010, but, I am sure that you can save the macro in your VBAProject(PERSONAL.XLSB) file, in a code Module.

If you go into the VBA Editor, see if you have a VBAProject(PERSONAL.XLSB) file.

If not see if the following link will help you create it:

How do I create and use a PERSONAL file for my VBA code
How do I create and use a PERSONAL file for my VBA code


You can then add my macro code, and, be able to run the macro from your VBAProject(PERSONAL.XLSB) file.

 

[cat57]

Thank you!!!

 

[hiker95]

cat57,

You are very welcome. Glad I could help.

And, come back anytime.

 

[jennifer865]

Hi Hiker95,

That macros is brilliant! Can it be applied to 3 sets of data?

1. I have a PC.
2. I am using Excel2010.

Here is my sample data:

The first set of data is shown in columns A-C, the second set is columns E-G, and 3rd set is columns I-K. The data should be sorted and matched by Asset # (Columns A, E & I).

	A	B	C	D	E	F	G	H	I	J	K
1	ASSET #	ASSET DESCRIPTION	COST		ASSET #	ASSET DESCRIPTION	COST		ASSET #	ASSET DESCRIPTION	COST
2	0001	WAREHOUSE	10,000,000		0001	WAREHOUSE	10,000,000		0006	YARD TRACTOR	40,000
3	0002	OFFICE BLDG	2,000,000		0002	OFFICE BLDG	2,000,000		0007	FORKLIFT	20,000
4	0003	GARAGE	3,000,000		0003	GARAGE	3,000,000		0008	CHEVY VAN	20,000
5	0004	14 ACRE CORP CAMPUS	5,000,000		0004	14 ACRE CORP CAMPUS	5,000,000		0012	COPIER	1,000
6	0005	GMC PICKUP TRUCK	20,000		0005	GMC PICKUP TRUCK	20,000		0014	SCISSOR LIFT	10,000
7	0006	YARD TRACTOR	40,000		0006	YARD TRACTOR	40,000		0001	WAREHOUSE	10,000,000
8	0007	FORKLIFT	20,000		0007	FORKLIFT	20,000		0002	OFFICE BLDG	2,000,000
9	0008	CHEVY VAN	20,000		0008	CHEVY VAN	20,000		0003	GARAGE	3,000,000
10	0009	CONFERENCE DESK	1,000		0009	CONFERENCE DESK	1,000		0004	14 ACRE CORP CAMPUS	5,000,000
11	0010	PHONE SYSTEM	2,000		0010	PHONE SYSTEM	2,000		0005	GMC PICKUP TRUCK	20,000
12	0011	REFRIGERATOR	1,000		0012	COPIER	1,000		0009	CONFERENCE DESK	1,000
13	0012	COPIER	1,000		0013	MERCHANDISE PICKER	10,000,000		0013	MERCHANDISE PICKER	10,000,000
14	0013	MERCHANDISE PICKER	10,000,000		0014	SCISSOR LIFT	10,000		0017	DELL 300	1,000
15	0014	SCISSOR LIFT	10,000		0015	LABEL READER	100,000
16	0015	LABEL READER	100,000		0017	DELL 300	1,000
17	0016	CONVEYOR	1,000,000		0018	INTEL SPIRON	1,000
18	0017	DELL 300	1,000
19	0018	INTEL SPIRON	1,000
20	0019	PACKER II SOFTWARE	5,000
21	0020	PACKER SOFTWARE UPGARDE	3,000

<tbody>
</tbody>

Thank you in advance! First time poster, so if I need to clean up my sample data, just let me know.

 

[hiker95]

jennifer865,

Welcome to the MrExcel forum.

That macros is brilliant!

Thanks for the feedback, and, kudos.

Can it be applied to 3 sets of data?

No, but I do have another way to solve your request.


To start with:

Your raw data is not the same as cat57's.

Please do not post your questions in threads started by others - - this is known as thread hijacking.

Always start a new thread for your questions and, if you think it helps, provide a link to any other thread as a reference.

Start a new thread for your question and be sure to give it a search friendly title that accurately describes your need.

In your New Thread include:
1. the version of Windows, and, Excel you are using
2. Are you using a PC or a Mac?

So that we can get it right on the first try:

Can you post a screenshot of the actual raw data worksheet?

And, can you post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

To post a small screen shot try one of the following:

Excel Jeanie
Download

MrExcel HTML Maker
https://onedrive.live.com/?cid=8cffdec0ce27e813&sc=documents&id=8CFFDEC0CE27E813!189

Borders-Copy-Paste
http://www.mrexcel.com/forum/about-board/444901-how-create-table-like-aladin.html#post2198045

To test the above:
Test Here


Or, you can upload your workbook to Box Net,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.


Then send me a Private Message, with a link to this thread as a reference, and, a link to your new thread, and, I will have a look.