Matching table rows
 

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

Matching table rows
 

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

Matching table rows
 

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??


--
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