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

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
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

M
  • Question
Excel Time Keeping "Application"
Replies
0
Views
164
Mrock1
M
T
  • Question
Flattening a hierarchy from 5 columns into a unique one
Replies
1
Views
541
Kerryx
K
M
  • Question
Return "yes" or "no" matching multiple criteria
Replies
5
Views
2K
MG137
M
R
  • Question
Can you help with this code please?
Replies
3
Views
190
rlv01
R
M
  • Question
Returning Values Based on Referent Cells (Avoiding Redundant Values)
Replies
5
Views
315
Fluff
Fluff

Forum statistics

Threads
1,216,458
Messages
6,130,757
Members
449,588
Latest member
accountant606

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