Dynamically aligning rows with matching records

T

tdguy09

New Member
Joined
Sep 16, 2009
Messages
11
Hi All. This is my first time posting. I have a question regarding a task that I need to complete quite urgently. I am reasonalbly familiar with excel and use most of the more commonly-used function and formulas. However when it comes to macros, i have had limited success, not having a true developer background. Since the task I need to perform cannot be done using a standard excel function(i'm pretty sure) I believe the only solution would involve some code, i.e. a macro.

So at a basic level I have to compare two recordsets. One from PROD and one from UAT. The objective is to establish any missing records from BOTH lists... and therein lies the complexity. Since its not a given that either list is complete I have to check both ways.

To add to the complexity, the list is built hierarchically so it is not a flat comparison i.e. the first 10 records/rows may match on LEVEL1 (e.g. A-A; B-B; C-C) but not on LEVEL2 (A1-A1;A2-(no match); etc.

THE EXCEL PART
I have placed the two recordsets side by side on a single worksheet. So lets say the first couple of records match exactly so the rows are perfectly aligned next to each other. Then, because there are more records in PROD at LEVEL2 of the hierarchy the rows start misaligning (this works both ways where there are more records at LEVEL2 for UAT). What I am trying to do as a first step is to align all records based on LEVEL1 of the the hierarchy. Put another way I want Excel to dynamically take the value (and corresponding row) from the one list e.g. the PROD list and line it up with the matching value from the UAT list. But here is the kicker. Excel must be able to determine for any particular LEVEL1 value whether there are more records in UAT or in PROD so that it aligns the records from either side.

I have tried to give as much information so that you have as clear a picture as possible without completely confusing you. I fear that I have failed but hopefully not. I will answer any questions to clarify. I can also post a small subset of the actuall records from excel if that will help with visualisation.

Would appreciate any help. THANKS!!
 
Sort by date Sort by votes
Hi, Not Entirely sure if this will help !!
If you have two lists, Column "A" and "B" and you want to know what is in "A" but not in "B", and what is in "B" but not in "A" then this will give you the Results in "Columns "G & H",
Code: 
[COLOR="Navy"]Sub[/COLOR] MG16Sep59
[COLOR="Navy"]Dim[/COLOR] Rng1 [COLOR="Navy"]As[/COLOR] Range, rng2 [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, RngAll [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Q [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer,[/COLOR] nRay, C [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer,[/COLOR] cc [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] oMax [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng1 = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
    [COLOR="Navy"]Set[/COLOR] rng2 = Range(Range("B1"), Range("B" & Rows.Count).End(xlUp))
        [COLOR="Navy"]Set[/COLOR] RngAll = Union(Rng1, rng2)
            ReDim Ray(1 To RngAll.Count, 1 To 2)
                ReDim nRay(1 To RngAll.Count, 1 To 2)
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] RngAll
        [COLOR="Navy"]If[/COLOR] Not .Exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
            n = n + 1
            .Add Dn.Value, n
            Ray(n, 1) = Dn.Value: Ray(n, 2) = Dn.Column
        [COLOR="Navy"]Else[/COLOR]
            Ray(.Item(Dn.Value), 1) = ""
            Ray(.Item(Dn.Value), 2) = ""
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] With
nRay(1, 1) = """A"" Not Found in ""B"""
    nRay(1, 2) = """B"" Not Found in ""A"""
        C = 1: cc = 1
[COLOR="Navy"]For[/COLOR] Q = 1 To UBound(Ray)
    [COLOR="Navy"]If[/COLOR] Ray(Q, 1) <> "" [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]If[/COLOR] Ray(Q, 2) = 1 [COLOR="Navy"]Then[/COLOR]
            C = C + 1
            nRay(C, 1) = Ray(Q, 1)
        [COLOR="Navy"]ElseIf[/COLOR] Ray(Q, 2) = 2 [COLOR="Navy"]Then[/COLOR]
            cc = cc + 1
            nRay(cc, 2) = Ray(Q, 1)
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]End[/COLOR] If
oMax = Application.Max(cc, C)
[COLOR="Navy"]Next[/COLOR] Q
[COLOR="Navy"]With[/COLOR] Range("G1")
    .Resize(oMax, 2) = nRay
    .Columns.Resize(, 2).AutoFit
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
bruderbell said:
http://www.mrexcel.com/forum/showthread.php?t=89356

or save it as a text file and then copy paste? That's worth a shot
Click to expand...


I have pasted the recordset below. It contains both sets of information (PROD and UAT) side by side as I described in my email. I have added the column letters(A-U) in the first row only in case it helps with alignment but they are not part of the recordset and can be removed.

The subset for UAT ends at column J and the subset for PROD begins at column L. The space (what would be column K) can be removed.

COLUMN A would represent the first level of the hierarchy (2 records i.e. "CURRENT ASSETS" and "DEP W/OTHER BKS OPER. BAL NIB").
COLUMN B and C would represent the second level of the hierarchy (3 records for "CURRENT ASSETS" i.e. "N050099","N100099","N150599" and their accompanying description.
COLUMN D-J would represent the third and final level with one row/record for "N050099", one row/record for "N100099", and two rows/records for "N150599".

This is what I need to compare to the second recordset (beginning at COLUMN L) which is laid out identically except that they do not always have the same amount of records per hierarchical level e.g. "N050099" in COLUMN M has 3 rows/records as opposed to "N050099" in COLUMN B which has only one row/record.

The added complexity is sometimes there are more records in PROD and sometimes there are more records in UAT. I imagine that if there were always more records in the one then the code would also be a lot simpler.

Anyway hope this all makes sense (or at least most of it). Feel free to ask questions. THANKS!



<TABLE dir=ltr cellSpacing=0 cellPadding=2 width=2387 border=0><TBODY><TR><TD width="9%" height=16>A

</TD><TD width="2%" height=16>B

</TD><TD width="17%" height=16>C

</TD><TD width="4%" height=16>D

</TD><TD width="3%" height=16>E

</TD><TD width="3%" height=16>F

</TD><TD width="3%" height=16>G

</TD><TD width="3%" height=16>H

</TD><TD width="3%" height=16>I

</TD><TD width="3%" height=16>J

</TD><TD width="1%" height=16>

</TD><TD width="9%" height=16>L

</TD><TD width="2%" height=16>M

</TD><TD width="17%" height=16>N

</TD><TD width="4%" height=16>O

</TD><TD width="3%" height=16>P

</TD><TD width="3%" height=16>Q

</TD><TD width="3%" height=16>R

</TD><TD width="3%" height=16>S

</TD><TD width="3%" height=16>T

</TD><TD width="3%" height=16>U

</TD></TR><TR><TD width="9%" height=14>RPT LINES

</TD><TD width="2%" height=14>

</TD><TD width="17%" height=14>

</TD><TD width="4%" height=14>G/L ACCOUNTS

</TD><TD width="3%" height=14>

</TD><TD width="3%" height=14>

</TD><TD width="3%" height=14>

</TD><TD width="3%" height=14>

</TD><TD width="3%" height=14>

</TD><TD width="3%" height=14>

</TD><TD width="1%" height=14>

</TD><TD width="9%" height=14>RPT LINES

</TD><TD width="2%" height=14>

</TD><TD width="17%" height=14>

</TD><TD width="4%" height=14>G/L ACCOUNTS

</TD><TD width="3%" height=14>

</TD><TD width="3%" height=14>

</TD><TD width="3%" height=14>

</TD><TD width="3%" height=14>

</TD><TD width="3%" height=14>

</TD><TD width="3%" height=14>

</TD></TR><TR><TD width="9%" height=14>A&L SUM

</TD><TD width="2%" height=14>A&L LINE

</TD><TD width="17%" height=14>DESCRIPTION

</TD><TD width="4%" height=14>ACCT MIN

</TD><TD width="3%" height=14>ACCT MAX

</TD><TD width="3%" height=14>PROD MIN

</TD><TD width="3%" height=14>PROD MAX

</TD><TD width="3%" height=14>CUST MIN

</TD><TD width="3%" height=14>CUST MAX

</TD><TD width="3%" height=14>RES TYPE

</TD><TD width="1%" height=14>

</TD><TD width="9%" height=14>A&L SUM

</TD><TD width="2%" height=14>A&L LINE

</TD><TD width="17%" height=14>DESCRIPTION

</TD><TD width="4%" height=14>ACCT MIN

</TD><TD width="3%" height=14>ACCT MAX

</TD><TD width="3%" height=14>PROD MIN

</TD><TD width="3%" height=14>PROD MAX

</TD><TD width="3%" height=14>CUST MIN

</TD><TD width="3%" height=14>CUST MAX

</TD><TD width="3%" height=14>RES TYPE

</TD></TR><TR><TD width="9%" height=16>CURRENT ASSETS

</TD><TD width="2%" height=16>

</TD><TD width="17%" height=16>

</TD><TD width="4%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="1%" height=16>

</TD><TD width="9%" height=16>CURRENT ASSETS

</TD><TD width="2%" height=16>

</TD><TD width="17%" height=16>

</TD><TD width="4%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD></TR><TR><TD width="9%" height=16>

</TD><TD width="2%" height=16>N050099

</TD><TD width="17%" height=16>GOLD COIN & GOLD/SILVER BULLION - US$

</TD><TD width="4%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="1%" height=16>

</TD><TD width="9%" height=16>

</TD><TD width="2%" height=16>N050099

</TD><TD width="17%" height=16>GOLD COIN & GOLD/SILVER BULLION - US$

</TD><TD width="4%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD></TR><TR><TD width="9%" height=16>

</TD><TD width="2%" height=16>

</TD><TD width="17%" height=16>

</TD><TD width="4%" height=16>0100

</TD><TD width="3%" height=16>1006

</TD><TD width="3%" height=16>80120

</TD><TD width="3%" height=16>80121

</TD><TD width="3%" height=16>E000

</TD><TD width="3%" height=16>E999

</TD><TD width="3%" height=16>ALL

</TD><TD width="1%" height=16>

</TD><TD width="9%" height=16>

</TD><TD width="2%" height=16>

</TD><TD width="17%" height=16>

</TD><TD width="4%" height=16>0100

</TD><TD width="3%" height=16>1006

</TD><TD width="3%" height=16>80120

</TD><TD width="3%" height=16>80121

</TD><TD width="3%" height=16>E000

</TD><TD width="3%" height=16>E999

</TD><TD width="3%" height=16>ALL

</TD></TR><TR><TD width="9%" height=16>

</TD><TD width="2%" height=16>N100099

</TD><TD width="17%" height=16>OTHER COIN OUTSIDE CANADA

</TD><TD width="4%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="1%" height=16>

</TD><TD width="9%" height=16>

</TD><TD width="2%" height=16>

</TD><TD width="17%" height=16>

</TD><TD width="4%" height=16>1010

</TD><TD width="3%" height=16>1010

</TD><TD width="3%" height=16>80120

</TD><TD width="3%" height=16>80121

</TD><TD width="3%" height=16>E300

</TD><TD width="3%" height=16>E300

</TD><TD width="3%" height=16>ALL

</TD></TR><TR><TD width="9%" height=16>

</TD><TD width="2%" height=16>

</TD><TD width="17%" height=16>

</TD><TD width="4%" height=16>0100

</TD><TD width="3%" height=16>1006

</TD><TD width="3%" height=16>80105

</TD><TD width="3%" height=16>80115

</TD><TD width="3%" height=16>E000

</TD><TD width="3%" height=16>E999

</TD><TD width="3%" height=16><>CA

</TD><TD width="1%" height=16>

</TD><TD width="9%" height=16>

</TD><TD width="2%" height=16>

</TD><TD width="17%" height=16>

</TD><TD width="4%" height=16>1010

</TD><TD width="3%" height=16>1010

</TD><TD width="3%" height=16>80120

</TD><TD width="3%" height=16>80121

</TD><TD width="3%" height=16>E901

</TD><TD width="3%" height=16>E901

</TD><TD width="3%" height=16>ALL

</TD></TR><TR><TD width="9%" height=16>

</TD><TD width="2%" height=16>N150599

</TD><TD width="17%" height=16>GOVERNMENT AND BANK NOTES

</TD><TD width="4%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="1%" height=16>

</TD><TD width="9%" height=16>

</TD><TD width="2%" height=16>N100099

</TD><TD width="17%" height=16>OTHER COIN OUTSIDE CANADA

</TD><TD width="4%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD></TR><TR><TD width="9%" height=16>

</TD><TD width="2%" height=16>

</TD><TD width="17%" height=16>

</TD><TD width="4%" height=16>0100

</TD><TD width="3%" height=16>1006

</TD><TD width="3%" height=16>80050

</TD><TD width="3%" height=16>80050

</TD><TD width="3%" height=16>E000

</TD><TD width="3%" height=16>E999

</TD><TD width="3%" height=16>ALL

</TD><TD width="1%" height=16>

</TD><TD width="9%" height=16>

</TD><TD width="2%" height=16>

</TD><TD width="17%" height=16>

</TD><TD width="4%" height=16>0100

</TD><TD width="3%" height=16>1006

</TD><TD width="3%" height=16>80105

</TD><TD width="3%" height=16>80115

</TD><TD width="3%" height=16>E000

</TD><TD width="3%" height=16>E999

</TD><TD width="3%" height=16><>CA

</TD></TR><TR><TD width="9%" height=16>

</TD><TD width="2%" height=16>

</TD><TD width="17%" height=16>

</TD><TD width="4%" height=16>0100

</TD><TD width="3%" height=16>1006

</TD><TD width="3%" height=16>80100

</TD><TD width="3%" height=16>80100

</TD><TD width="3%" height=16>E000

</TD><TD width="3%" height=16>E999

</TD><TD width="3%" height=16>ALL

</TD><TD width="1%" height=16>

</TD><TD width="9%" height=16>

</TD><TD width="2%" height=16>N150599

</TD><TD width="17%" height=16>GOVERNMENT AND BANK NOTES

</TD><TD width="4%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD></TR><TR><TD width="9%" height=16>DEP W/OTHER BKS OPER. BAL NIB

</TD><TD width="2%" height=16>

</TD><TD width="17%" height=16>

</TD><TD width="4%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="1%" height=16>

</TD><TD width="9%" height=16>

</TD><TD width="2%" height=16>

</TD><TD width="17%" height=16>

</TD><TD width="4%" height=16>0100

</TD><TD width="3%" height=16>1006

</TD><TD width="3%" height=16>80050

</TD><TD width="3%" height=16>80050

</TD><TD width="3%" height=16>E000

</TD><TD width="3%" height=16>E999

</TD><TD width="3%" height=16>ALL

</TD></TR><TR><TD width="9%" height=16>

</TD><TD width="2%" height=16>N200599

</TD><TD width="17%" height=16>DEP W/OTHER BKS ACCEPTANCES

</TD><TD width="4%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="1%" height=16>

</TD><TD width="9%" height=16>

</TD><TD width="2%" height=16>

</TD><TD width="17%" height=16>

</TD><TD width="4%" height=16>0100

</TD><TD width="3%" height=16>1006

</TD><TD width="3%" height=16>80100

</TD><TD width="3%" height=16>80100

</TD><TD width="3%" height=16>E000

</TD><TD width="3%" height=16>E999

</TD><TD width="3%" height=16>ALL

</TD></TR><TR><TD width="9%" height=16>

</TD><TD width="2%" height=16>

</TD><TD width="17%" height=16>

</TD><TD width="4%" height=16>1000

</TD><TD width="3%" height=16>1010

</TD><TD width="3%" height=16>01920

</TD><TD width="3%" height=16>01920

</TD><TD width="3%" height=16>E800

</TD><TD width="3%" height=16>E803

</TD><TD width="3%" height=16>ALL

</TD><TD width="1%" height=16>

</TD><TD width="9%" height=16>DEP W/OTHER BKS OPER. BAL NIB

</TD><TD width="2%" height=16>

</TD><TD width="17%" height=16>

</TD><TD width="4%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD></TR><TR><TD width="9%" height=16>

</TD><TD width="2%" height=16>N201099

</TD><TD width="17%" height=16>DEP W/OTHER BKS TERM DEPOSITS

</TD><TD width="4%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="1%" height=16>

</TD><TD width="9%" height=16>

</TD><TD width="2%" height=16>N200599

</TD><TD width="17%" height=16>DEP W/OTHER BKS ACCEPTANCES

</TD><TD width="4%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD></TR><TR><TD width="9%" height=16>

</TD><TD width="2%" height=16>

</TD><TD width="17%" height=16>

</TD><TD width="4%" height=16>1000

</TD><TD width="3%" height=16>1003

</TD><TD width="3%" height=16>01961

</TD><TD width="3%" height=16>01962

</TD><TD width="3%" height=16>E800

</TD><TD width="3%" height=16>E803

</TD><TD width="3%" height=16>ALL

</TD><TD width="1%" height=16>

</TD><TD width="9%" height=16>

</TD><TD width="2%" height=16>

</TD><TD width="17%" height=16>

</TD><TD width="4%" height=16>1000

</TD><TD width="3%" height=16>1010

</TD><TD width="3%" height=16>01920

</TD><TD width="3%" height=16>01920

</TD><TD width="3%" height=16>E800

</TD><TD width="3%" height=16>E803

</TD><TD width="3%" height=16>ALL

</TD></TR><TR><TD width="9%" height=16>

</TD><TD width="2%" height=16>

</TD><TD width="17%" height=16>

</TD><TD width="4%" height=16>1000

</TD><TD width="3%" height=16>1010

</TD><TD width="3%" height=16>01930

</TD><TD width="3%" height=16>01939

</TD><TD width="3%" height=16>E800

</TD><TD width="3%" height=16>E803

</TD><TD width="3%" height=16>ALL

</TD><TD width="1%" height=16>

</TD><TD width="9%" height=16>

</TD><TD width="2%" height=16>N201099

</TD><TD width="17%" height=16>DEP W/OTHER BKS TERM DEPOSITS

</TD><TD width="4%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD></TR><TR><TD width="9%" height=16>

</TD><TD width="2%" height=16>

</TD><TD width="17%" height=16>

</TD><TD width="4%" height=16>1000

</TD><TD width="3%" height=16>1010

</TD><TD width="3%" height=16>01941

</TD><TD width="3%" height=16>01951

</TD><TD width="3%" height=16>E800

</TD><TD width="3%" height=16>E803

</TD><TD width="3%" height=16>ALL

</TD><TD width="1%" height=16>

</TD><TD width="9%" height=16>

</TD><TD width="2%" height=16>

</TD><TD width="17%" height=16>

</TD><TD width="4%" height=16>1000

</TD><TD width="3%" height=16>1003

</TD><TD width="3%" height=16>01961

</TD><TD width="3%" height=16>01962

</TD><TD width="3%" height=16>E800

</TD><TD width="3%" height=16>E803

</TD><TD width="3%" height=16>ALL

</TD></TR><TR><TD width="9%" height=16>

</TD><TD width="2%" height=16>N201399

</TD><TD width="17%" height=16>DEP W/OTHER BKS TERM DEPS TRDG

</TD><TD width="4%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="1%" height=16>

</TD><TD width="9%" height=16>

</TD><TD width="2%" height=16>

</TD><TD width="17%" height=16>

</TD><TD width="4%" height=16>1000

</TD><TD width="3%" height=16>1010

</TD><TD width="3%" height=16>01930

</TD><TD width="3%" height=16>01939

</TD><TD width="3%" height=16>E800

</TD><TD width="3%" height=16>E803

</TD><TD width="3%" height=16>ALL

</TD></TR><TR><TD width="9%" height=16>

</TD><TD width="2%" height=16>

</TD><TD width="17%" height=16>

</TD><TD width="4%" height=16>1000

</TD><TD width="3%" height=16>1010

</TD><TD width="3%" height=16>01940

</TD><TD width="3%" height=16>01940

</TD><TD width="3%" height=16>E800

</TD><TD width="3%" height=16>E803

</TD><TD width="3%" height=16>ALL

</TD><TD width="1%" height=16>

</TD><TD width="9%" height=16>

</TD><TD width="2%" height=16>

</TD><TD width="17%" height=16>

</TD><TD width="4%" height=16>1000

</TD><TD width="3%" height=16>1010

</TD><TD width="3%" height=16>01941

</TD><TD width="3%" height=16>01951

</TD><TD width="3%" height=16>E800

</TD><TD width="3%" height=16>E803

</TD><TD width="3%" height=16>ALL

</TD></TR><TR><TD width="9%" height=16>

</TD><TD width="2%" height=16>

</TD><TD width="17%" height=16>

</TD><TD width="4%" height=16>1000

</TD><TD width="3%" height=16>1010

</TD><TD width="3%" height=16>04712

</TD><TD width="3%" height=16>04712

</TD><TD width="3%" height=16>E800

</TD><TD width="3%" height=16>E803

</TD><TD width="3%" height=16>ALL

</TD><TD width="1%" height=16>

</TD><TD width="9%" height=16>

</TD><TD width="2%" height=16>N201399

</TD><TD width="17%" height=16>DEP W/OTHER BKS TERM DEPS TRDG

</TD><TD width="4%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD></TR><TR><TD width="9%" height=16>

</TD><TD width="2%" height=16>

</TD><TD width="17%" height=16>

</TD><TD width="4%" height=16>1200

</TD><TD width="3%" height=16>1209

</TD><TD width="3%" height=16>04712

</TD><TD width="3%" height=16>04712

</TD><TD width="3%" height=16>E800

</TD><TD width="3%" height=16>E803

</TD><TD width="3%" height=16>ALL

</TD><TD width="1%" height=16>

</TD><TD width="9%" height=16>

</TD><TD width="2%" height=16>

</TD><TD width="17%" height=16>

</TD><TD width="4%" height=16>1000

</TD><TD width="3%" height=16>1010

</TD><TD width="3%" height=16>01940

</TD><TD width="3%" height=16>01940

</TD><TD width="3%" height=16>E800

</TD><TD width="3%" height=16>E803

</TD><TD width="3%" height=16>ALL

</TD></TR><TR><TD width="9%" height=16>

</TD><TD width="2%" height=16>N201599

</TD><TD width="17%" height=16>GOLD /SILVER DEPOSITS US$

</TD><TD width="4%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="1%" height=16>

</TD><TD width="9%" height=16>

</TD><TD width="2%" height=16>

</TD><TD width="17%" height=16>

</TD><TD width="4%" height=16>1000

</TD><TD width="3%" height=16>1010

</TD><TD width="3%" height=16>04712

</TD><TD width="3%" height=16>04712

</TD><TD width="3%" height=16>E800

</TD><TD width="3%" height=16>E803

</TD><TD width="3%" height=16>ALL

</TD></TR><TR><TD width="9%" height=16>

</TD><TD width="2%" height=16>

</TD><TD width="17%" height=16>

</TD><TD width="4%" height=16>1000

</TD><TD width="3%" height=16>1006

</TD><TD width="3%" height=16>80130

</TD><TD width="3%" height=16>80131

</TD><TD width="3%" height=16>E000

</TD><TD width="3%" height=16>E901

</TD><TD width="3%" height=16>ALL

</TD><TD width="1%" height=16>

</TD><TD width="9%" height=16>

</TD><TD width="2%" height=16>

</TD><TD width="17%" height=16>

</TD><TD width="4%" height=16>1200

</TD><TD width="3%" height=16>1209

</TD><TD width="3%" height=16>04712

</TD><TD width="3%" height=16>04712

</TD><TD width="3%" height=16>E800

</TD><TD width="3%" height=16>E803

</TD><TD width="3%" height=16>ALL

</TD></TR><TR><TD width="9%" height=16>

</TD><TD width="2%" height=16>N202099

</TD><TD width="17%" height=16>OTHER DEPOSITS - OPERATING BALANCES - INTEREST BEARING

</TD><TD width="4%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="1%" height=16>

</TD><TD width="9%" height=16>

</TD><TD width="2%" height=16>N201599

</TD><TD width="17%" height=16>GOLD /SILVER DEPOSITS US$

</TD><TD width="4%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD></TR><TR><TD width="9%" height=16>

</TD><TD width="2%" height=16>

</TD><TD width="17%" height=16>

</TD><TD width="4%" height=16>1000

</TD><TD width="3%" height=16>1000

</TD><TD width="3%" height=16>01910

</TD><TD width="3%" height=16>01910

</TD><TD width="3%" height=16>E800

</TD><TD width="3%" height=16>E803

</TD><TD width="3%" height=16>ALL

</TD><TD width="1%" height=16>

</TD><TD width="9%" height=16>

</TD><TD width="2%" height=16>

</TD><TD width="17%" height=16>

</TD><TD width="4%" height=16>1000

</TD><TD width="3%" height=16>1006

</TD><TD width="3%" height=16>80130

</TD><TD width="3%" height=16>80131

</TD><TD width="3%" height=16>E000

</TD><TD width="3%" height=16>E901

</TD><TD width="3%" height=16>ALL

</TD></TR><TR><TD width="9%" height=16>

</TD><TD width="2%" height=16>N202399

</TD><TD width="17%" height=16>BANK OF AMERICA

</TD><TD width="4%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="1%" height=16>

</TD><TD width="9%" height=16>

</TD><TD width="2%" height=16>N202099

</TD><TD width="17%" height=16>OTHER DEPOSITS - OPERATING BALANCES - INTEREST BEARING

</TD><TD width="4%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD></TR><TR><TD width="9%" height=16>

</TD><TD width="2%" height=16>

</TD><TD width="17%" height=16>

</TD><TD width="4%" height=16>1000

</TD><TD width="3%" height=16>1000

</TD><TD width="3%" height=16>88100

</TD><TD width="3%" height=16>88100

</TD><TD width="3%" height=16>E800

</TD><TD width="3%" height=16>E800

</TD><TD width="3%" height=16>ALL

</TD><TD width="1%" height=16>

</TD><TD width="9%" height=16>

</TD><TD width="2%" height=16>

</TD><TD width="17%" height=16>

</TD><TD width="4%" height=16>1000

</TD><TD width="3%" height=16>1000

</TD><TD width="3%" height=16>01910

</TD><TD width="3%" height=16>01910

</TD><TD width="3%" height=16>E800

</TD><TD width="3%" height=16>E803

</TD><TD width="3%" height=16>ALL

</TD></TR><TR><TD width="9%" height=16>

</TD><TD width="2%" height=16>

</TD><TD width="17%" height=16>

</TD><TD width="4%" height=16>1000

</TD><TD width="3%" height=16>1000

</TD><TD width="3%" height=16>88101

</TD><TD width="3%" height=16>88101

</TD><TD width="3%" height=16>9996

</TD><TD width="3%" height=16>9996

</TD><TD width="3%" height=16>ALL

</TD><TD width="1%" height=16>

</TD><TD width="9%" height=16>

</TD><TD width="2%" height=16>N202399

</TD><TD width="17%" height=16>BANK OF AMERICA

</TD><TD width="4%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD></TR><TR><TD width="9%" height=16>

</TD><TD width="2%" height=16>

</TD><TD width="17%" height=16>

</TD><TD width="4%" height=16>1000

</TD><TD width="3%" height=16>1000

</TD><TD width="3%" height=16>88101

</TD><TD width="3%" height=16>88101

</TD><TD width="3%" height=16>9997

</TD><TD width="3%" height=16>9997

</TD><TD width="3%" height=16>ALL

</TD><TD width="1%" height=16>

</TD><TD width="9%" height=16>

</TD><TD width="2%" height=16>

</TD><TD width="17%" height=16>

</TD><TD width="4%" height=16>1000

</TD><TD width="3%" height=16>1000

</TD><TD width="3%" height=16>88100

</TD><TD width="3%" height=16>88100

</TD><TD width="3%" height=16>E800

</TD><TD width="3%" height=16>E800

</TD><TD width="3%" height=16>ALL

</TD></TR><TR><TD width="9%" height=16>

</TD><TD width="2%" height=16>

</TD><TD width="17%" height=16>

</TD><TD width="4%" height=16>1000

</TD><TD width="3%" height=16>1000

</TD><TD width="3%" height=16>88101

</TD><TD width="3%" height=16>88101

</TD><TD width="3%" height=16>9998

</TD><TD width="3%" height=16>9998

</TD><TD width="3%" height=16>ALL

</TD><TD width="1%" height=16>

</TD><TD width="9%" height=16>

</TD><TD width="2%" height=16>

</TD><TD width="17%" height=16>

</TD><TD width="4%" height=16>1000

</TD><TD width="3%" height=16>1000

</TD><TD width="3%" height=16>88101

</TD><TD width="3%" height=16>88101

</TD><TD width="3%" height=16>9996

</TD><TD width="3%" height=16>9996

</TD><TD width="3%" height=16>ALL

</TD></TR><TR><TD width="9%" height=16>

</TD><TD width="2%" height=16>N202599

</TD><TD width="17%" height=16>DEP W/OTHER BKS OPER. BAL NIB

</TD><TD width="4%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="1%" height=16>

</TD><TD width="9%" height=16>

</TD><TD width="2%" height=16>

</TD><TD width="17%" height=16>

</TD><TD width="4%" height=16>1000

</TD><TD width="3%" height=16>1000

</TD><TD width="3%" height=16>88101

</TD><TD width="3%" height=16>88101

</TD><TD width="3%" height=16>9997

</TD><TD width="3%" height=16>9997

</TD><TD width="3%" height=16>ALL

</TD></TR><TR><TD width="9%" height=16>

</TD><TD width="2%" height=16>

</TD><TD width="17%" height=16>

</TD><TD width="4%" height=16>1000

</TD><TD width="3%" height=16>1000

</TD><TD width="3%" height=16>01911

</TD><TD width="3%" height=16>01911

</TD><TD width="3%" height=16>9033

</TD><TD width="3%" height=16>9033

</TD><TD width="3%" height=16>ALL

</TD><TD width="1%" height=16>

</TD><TD width="9%" height=16>

</TD><TD width="2%" height=16>

</TD><TD width="17%" height=16>

</TD><TD width="4%" height=16>1000

</TD><TD width="3%" height=16>1000

</TD><TD width="3%" height=16>88101

</TD><TD width="3%" height=16>88101

</TD><TD width="3%" height=16>9998

</TD><TD width="3%" height=16>9998

</TD><TD width="3%" height=16>ALL

</TD></TR><TR><TD width="9%" height=16>

</TD><TD width="2%" height=16>

</TD><TD width="17%" height=16>

</TD><TD width="4%" height=16>1000

</TD><TD width="3%" height=16>1000

</TD><TD width="3%" height=16>01911

</TD><TD width="3%" height=16>01911

</TD><TD width="3%" height=16>E800

</TD><TD width="3%" height=16>E803

</TD><TD width="3%" height=16>ALL

</TD><TD width="1%" height=16>

</TD><TD width="9%" height=16>

</TD><TD width="2%" height=16>N202599

</TD><TD width="17%" height=16>DEP W/OTHER BKS OPER. BAL NIB

</TD><TD width="4%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD></TR><TR><TD width="9%" height=16>

</TD><TD width="2%" height=16>

</TD><TD width="17%" height=16>

</TD><TD width="4%" height=16>1000

</TD><TD width="3%" height=16>1006

</TD><TD width="3%" height=16>01911

</TD><TD width="3%" height=16>01911

</TD><TD width="3%" height=16>E305

</TD><TD width="3%" height=16>E305

</TD><TD width="3%" height=16>ALL

</TD><TD width="1%" height=16>

</TD><TD width="9%" height=16>

</TD><TD width="2%" height=16>

</TD><TD width="17%" height=16>

</TD><TD width="4%" height=16>1000

</TD><TD width="3%" height=16>1000

</TD><TD width="3%" height=16>01911

</TD><TD width="3%" height=16>01911

</TD><TD width="3%" height=16>9033

</TD><TD width="3%" height=16>9033

</TD><TD width="3%" height=16>ALL

</TD></TR><TR><TD width="9%" height=16>

</TD><TD width="2%" height=16>N203599

</TD><TD width="17%" height=16>FEDERAL FUNDS SOLD US$

</TD><TD width="4%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="1%" height=16>

</TD><TD width="9%" height=16>

</TD><TD width="2%" height=16>

</TD><TD width="17%" height=16>

</TD><TD width="4%" height=16>1000

</TD><TD width="3%" height=16>1000

</TD><TD width="3%" height=16>01911

</TD><TD width="3%" height=16>01911

</TD><TD width="3%" height=16>E800

</TD><TD width="3%" height=16>E803

</TD><TD width="3%" height=16>ALL

</TD></TR><TR><TD width="9%" height=16>

</TD><TD width="2%" height=16>

</TD><TD width="17%" height=16>

</TD><TD width="4%" height=16>1000

</TD><TD width="3%" height=16>1006

</TD><TD width="3%" height=16>80335

</TD><TD width="3%" height=16>80335

</TD><TD width="3%" height=16>E000

</TD><TD width="3%" height=16>E999

</TD><TD width="3%" height=16>ALL

</TD><TD width="1%" height=16>

</TD><TD width="9%" height=16>

</TD><TD width="2%" height=16>

</TD><TD width="17%" height=16>

</TD><TD width="4%" height=16>1000

</TD><TD width="3%" height=16>1006

</TD><TD width="3%" height=16>01911

</TD><TD width="3%" height=16>01911

</TD><TD width="3%" height=16>E305

</TD><TD width="3%" height=16>E305

</TD><TD width="3%" height=16>ALL

</TD></TR><TR><TD width="9%" height=16>

</TD><TD width="2%" height=16>

</TD><TD width="17%" height=16>

</TD><TD width="4%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="1%" height=16>

</TD><TD width="9%" height=16>

</TD><TD width="2%" height=16>N203599

</TD><TD width="17%" height=16>FEDERAL FUNDS SOLD US$

</TD><TD width="4%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD></TR><TR><TD width="9%" height=16>

</TD><TD width="2%" height=16>

</TD><TD width="17%" height=16>

</TD><TD width="4%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="3%" height=16>

</TD><TD width="1%" height=16>

</TD><TD width="9%" height=16>

</TD><TD width="2%" height=16>

</TD><TD width="17%" height=16>

</TD><TD width="4%" height=16>1000

</TD><TD width="3%" height=16>1006

</TD><TD width="3%" height=16>80335

</TD><TD width="3%" height=16>80335

</TD><TD width="3%" height=16>E000

</TD><TD width="3%" height=16>E999

</TD><TD width="3%" height=16>ALL

</TD></TR></TBODY></TABLE>
 
Upvote 0
MickG said:
Hi, Not Entirely sure if this will help !!
If you have two lists, Column "A" and "B" and you want to know what is in "A" but not in "B", and what is in "B" but not in "A" then this will give you the Results in "Columns "G & H",
Code: 
[COLOR=navy]Sub[/COLOR] MG16Sep59
[COLOR=navy]Dim[/COLOR] Rng1 [COLOR=navy]As[/COLOR] Range, rng2 [COLOR=navy]As[/COLOR] Range, Dn [COLOR=navy]As[/COLOR] Range, RngAll [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] n [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] Q [COLOR=navy]As[/COLOR] [COLOR=navy]Integer,[/COLOR] nRay, C [COLOR=navy]As[/COLOR] [COLOR=navy]Integer,[/COLOR] cc [COLOR=navy]As[/COLOR] [COLOR=navy]Integer[/COLOR]
[COLOR=navy]Dim[/COLOR] oMax [COLOR=navy]As[/COLOR] [COLOR=navy]Integer[/COLOR]
[COLOR=navy]Set[/COLOR] Rng1 = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
    [COLOR=navy]Set[/COLOR] rng2 = Range(Range("B1"), Range("B" & Rows.Count).End(xlUp))
        [COLOR=navy]Set[/COLOR] RngAll = Union(Rng1, rng2)
            ReDim Ray(1 To RngAll.Count, 1 To 2)
                ReDim nRay(1 To RngAll.Count, 1 To 2)
[COLOR=navy]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
    [COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] RngAll
        [COLOR=navy]If[/COLOR] Not .Exists(Dn.Value) [COLOR=navy]Then[/COLOR]
            n = n + 1
            .Add Dn.Value, n
            Ray(n, 1) = Dn.Value: Ray(n, 2) = Dn.Column
        [COLOR=navy]Else[/COLOR]
            Ray(.Item(Dn.Value), 1) = ""
            Ray(.Item(Dn.Value), 2) = ""
        [COLOR=navy]End[/COLOR] If
    [COLOR=navy]Next[/COLOR] Dn
[COLOR=navy]End[/COLOR] With
nRay(1, 1) = """A"" Not Found in ""B"""
    nRay(1, 2) = """B"" Not Found in ""A"""
        C = 1: cc = 1
[COLOR=navy]For[/COLOR] Q = 1 To UBound(Ray)
    [COLOR=navy]If[/COLOR] Ray(Q, 1) <> "" [COLOR=navy]Then[/COLOR]
        [COLOR=navy]If[/COLOR] Ray(Q, 2) = 1 [COLOR=navy]Then[/COLOR]
            C = C + 1
            nRay(C, 1) = Ray(Q, 1)
        [COLOR=navy]ElseIf[/COLOR] Ray(Q, 2) = 2 [COLOR=navy]Then[/COLOR]
            cc = cc + 1
            nRay(cc, 2) = Ray(Q, 1)
        [COLOR=navy]End[/COLOR] If
    [COLOR=navy]End[/COLOR] If
oMax = Application.Max(cc, C)
[COLOR=navy]Next[/COLOR] Q
[COLOR=navy]With[/COLOR] Range("G1")
    .Resize(oMax, 2) = nRay
    .Columns.Resize(, 2).AutoFit
[COLOR=navy]End[/COLOR] With
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]
Regards Mick
Click to expand...


Thanks for the code Mick,

What I really need though is code that will align the records and create blank spaces where there is not match. It is the actual physical movement of the record to align with a corresponding record that I am interested in. But thanks for the effort. Much appreciated.
 
Upvote 0
Hi, From the way your Sheet is layed out, I think aligning the groups would be quite difficult.
If you could take the individual groups for comparison, and align then in columns side by side, then the code below would align all the matching items and leave space where there is no match. If you laid out a new sheet in pairs of columns you could add a loop to the code to run through each group/Column Pair.
This particular code will align matching Data in columns "A" and "B" and overwrite the old Column "A" and "B" Data.
It's about the best I can do.
Code: 
[COLOR="Navy"]Sub[/COLOR] MG17Sep38
[COLOR="Navy"]Dim[/COLOR] Rng1 [COLOR="Navy"]As[/COLOR] Range, Rng2 [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] RngAll, Ray
[COLOR="Navy"]Set[/COLOR] Rng1 = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
    [COLOR="Navy"]Set[/COLOR] Rng2 = Range(Range("B1"), Range("B" & Rows.Count).End(xlUp))
        [COLOR="Navy"]Set[/COLOR] RngAll = Union(Rng1, Rng2)
             ReDim Ray(1 To Rows.Count, 1 To 2)
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] RngAll
        [COLOR="Navy"]If[/COLOR] Not .Exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
            n = n + 1
            .Add Dn.Value, n
                [COLOR="Navy"]If[/COLOR] Dn.Column = 1 [COLOR="Navy"]Then[/COLOR]
                    Ray(n, 1) = Dn.Value
                [COLOR="Navy"]Else[/COLOR]
                    Ray(n, 2) = Dn.Value
                [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]Else[/COLOR]
                [COLOR="Navy"]If[/COLOR] Dn.Column = 1 [COLOR="Navy"]Then[/COLOR]
                    Ray(.Item(Dn.Value), 1) = Dn.Value
                [COLOR="Navy"]Else[/COLOR]
                    Ray(.Item(Dn.Value), 2) = Dn.Value
                [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR]
RngAll.ClearContents
Range("A1").Resize(.Count, 2) = Ray
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question Question
Duplicate a Sales Engineer to an outside Sales Rep
3 4 5
Replies
40
Views
522
Holger
H
M
  • Question Question
VBA that pulls data based on multiple criteria
Replies
0
Views
368
Mouse7681
M
R
  • Solved
Compare two ranges in Excel to see if they match exactly
2 3
Replies
25
Views
574
HighAndWilder
H
W
  • Solved
VBA - Match two values from Table1 to two in Table2 and move/align
2
Replies
15
Views
700
Welshy1491
W
U
  • Question Question
Excel Activate workbook1/sheet, Copy/paste row in workbook2/sheet, activate autofilter, return value and loop
Replies
0
Views
211
user29383902
U

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top