insert range of cells if data in column doesn't match

[mperrah]

I am comparing data between two columns (other cells are adjoined).
There are other columns of data included that need to line up across the rows.
There are many items with multiple entries in column "E" and "F" that match.
When they don't match I want to add empty cells next to the unmatched item left or right.
So in the end i have every row having a single item matched across
or a single item and blank cells next to it (duplicates are expected).
i.e. If "E" has no match in "F" add cells to right,
if "F" has no match in "E" add cells to left.
Cells A-E are the first item, cells F-G are second item.
E and F are the cells to compare.
If F matches G do nothing.
If F doesn't match G add cells to F, G, H and shift down
If F is not in G, add cells to A, B, C, D, E and shift down

The data is sorted alpha/numerically top to bottom
I'm trying to get a sequential list of all items
based on the values of "E" and "F"

I have attached a file with a before and after sheet of sample data.

sorry to be so verbose, but I have found the more detailed at the beginning helps eliminate wasted efforts

- thanks in advance.
mark

[GTO]

Hi Mark,

There appears to be no file attached. Please try again, preferably in .xls format.

Thank you so much,

Mark

[mperrah]

first tab is raw data (before) second tab is goal for output (after)

[mperrah]

I used this formula to look for matches between 2 columns:
<code>
=ISNUMBER(MATCH(F2,List1,0))
</code>
But I'm having trouble inserting cells next to the non matching column data,
because the data across columns don't line up and dont have the same number of matching rows...
any ideas

[mperrah]

how do i compare values in column "F" and "G"
if they dont match count down from current "no-match" cell to the match and add that number of cells using resize and insert, shift down.
resize "F" back to "A" for width and the count to the match for Height

if "F" is not found in "G" then start from there and scan down for "G" in "F"
and count the number of cells till match then resize and insert.shift down
resize "G" to "I" then down to match and insert.shift down.

xlt?

[mperrah]

here is a better sample.
The other file was unsorted and had leading zeros causing errors.
Sorry

[mperrah]

found this code that does half what im trying to do.

[vba]

Sub alex()
Application.ScreenUpdating = False

Dim cell, cell2 As Range
Dim lr, lr2, r As Long

Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = "result"
Sheets(1).Activate
lr = Range("A" & Rows.Count).End(xlUp).row

For Each cell In Range("A1:A" & lr)

ActiveWorkbook.Sheets(2).Activate
lr2 = Range("A" & Rows.Count).End(xlUp).row
For Each cell2 In Range("A1:A" & lr2)
If cell2.Text = cell.Text Then

cell2.EntireRow.Copy Destination:=Sheets("result").Range("A" & r)
r = r + 1

End If

Next cell2
Next cell
Application.ScreenUpdating = True

End Sub

[/vba]


the match and copy to new sheet would be useful.
but also when no match copy those cells of the row to the next empty row and leave the cells to left or right blank in the new sheet
I'm comparing data on same sheet in column F and G.
If F has no match in G add cells from A to F and leave G to I empty
If G has no match in F add cells G to I and leave A to F empty.

any takers?
Your help is greatly appreciated
Found this code in post
http://www.vbaexpress.com/forum/showthread.php?t=43277