Matching table rows

[steev_jd]

Hi,

How / Is it possible to match entries in lists that are in a random order and
are different lengths. There are two columns in each list and i need to keep
rows from the same tables next to each other,

i.e.

From this

a¦1 a¦1
b¦2 c¦2
c¦2 d¦6
d¦7

to this
a¦1 a¦1
b¦2
c¦2 c¦2
d¦7 d¦6

Thanks in advance.

[Dave Peterson]

Are A|1 and A|1 just values in separate cells?

And shouldn't you have ended up with something like:

a¦1 a¦1
b¦2
c¦2 c¦2
d¦6
d¦7


Add a header to row 1 and try this macro that I've saved this from a few
previous posts:

Option Explicit
Sub testme()

Application.ScreenUpdating = False

Dim wks As Worksheet
Dim ColA As Range
Dim ColB As Range
Dim iRow As Long
Dim myCols As Long

Set wks = Worksheets("sheet1")
wks.DisplayPageBreaks = False
With wks
'row 1 has headers!
Set ColA = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
Set ColB = .Range("b2", .Cells(.Rows.Count, "B").End(xlUp))

With ColA
.Sort key1:=.Cells(1), order1:=xlAscending, header:=xlNo
End With

'change the mycols to the number of columns that
'are associated with column B

myCols = 1 ' columns B only
With ColB.Resize(, myCols)
.Sort key1:=.Cells(1), order1:=xlAscending, header:=xlNo
End With

iRow = 2
Do
If Application.CountA(.Cells(iRow, "A").Resize(1, 2)) = 0 Then
Exit Do
End If

If .Cells(iRow, "A").Value = .Cells(iRow, "B").Value _
Or Application.CountA(.Cells(iRow, "A").Resize(1, 2)) = 1 Then
'do nothing
Else
If .Cells(iRow, "A").Value > .Cells(iRow, "B").Value Then
.Cells(iRow, "A").Insert shift:=xlDown
Else
.Cells(iRow, "B").Resize(1, myCols).Insert shift:=xlDown
End If
End If
iRow = iRow + 1
Loop
End With

Application.ScreenUpdating = True

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

steev_jd wrote:
>
> Hi,
>
> How / Is it possible to match entries in lists that are in a random
> order and
> are different lengths. There are two columns in each list and i need to
> keep
> rows from the same tables next to each other,
>
> i.e.
>
> From this
>
> a¦1 a¦1
> b¦2 c¦2
> c¦2 d¦6
> d¦7
>
> to this
> a¦1 a¦1
> b¦2
> c¦2 c¦2
> d¦7 d¦6
>
> Thanks in advance.
>
> --
> steev_jd
> ------------------------------------------------------------------------
> steev_jd's Profile: http://www.excelforum.com/member.php...o&userid=33107
> View this thread: http://www.excelforum.com/showthread...hreadid=530482

--

Dave Peterson

[steev_jd]

Hi,

Thanks for that macro, I will try it once I have read up that link.
In answer to your questions
I have in total 4 columns of data, from two different spreadsheets, which i have put into the same spreadsheet.
The data basically shows products and sales, they should be the same but there are some inconsistencies.
Some products are missing from one spreadsheet or the other, and some sales figures are differing.
Therefore I want to sort the data so i have

Coke...¦...150...¦...Coke...¦...150.
Pepsi...¦..100...¦..Pepsi...¦...80...
Tango.¦...75....¦............¦.........
.........¦...........¦..Fanta..¦...45...

and can compare at a glance, or filter for blank fields etc.

Don't know if this makes any difference??