1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Need Excel 2007 help on comparing cells

Discussion in 'Business Applications' started by abgbpb, Dec 30, 2009.

Thread Status:
Not open for further replies.
Advertisement
  1. abgbpb

    abgbpb Thread Starter

    Joined:
    Dec 30, 2009
    Messages:
    3
    Good day -

    I need to compare cells in the same column in a BIG Excel 2007 file. When a change in the values is found, insert a row. Example:

    A801
    A801
    A801
    999B
    999B
    80C5
    80C5

    Using this example, a blank row would be added between the last A801 entry and the first 999B entry. And another blank row would be entered between the last 999B entry and the first 80C5 entry. I.e.,

    A801
    A801
    A801

    999B
    999B

    80C5
    80C5

    As you can tell from my example, the cells to be compared are alpha/numeric. Ideas?

    Thank you in advance. abgbpb
     
  2. Ent

    Ent Trusted Advisor

    Joined:
    Apr 11, 2009
    Messages:
    5,467
    First Name:
    Josiah
    Not unless you want to drop to macro coding. What I might suggest, if all you want is a way to mark the changing code, is using conditional formatting to colour a cell if it doesn't match the cell above.
     
  3. abgbpb

    abgbpb Thread Starter

    Joined:
    Dec 30, 2009
    Messages:
    3
    Found the solution:

    Sub BLANKLINE()
    '
    ' BLANKLINE Macro
    '

    '
    Rng = Selection.Rows.Count
    ActiveCell.Offset(0, 0).Select
    Application.ScreenUpdating = False
    For i = 1 To Rng
    If ActiveCell.Value = ActiveCell.Offset(1, 0).Value Then
    ActiveCell.Offset(1, 0).Select
    Else
    ActiveCell.Offset(1, 0).Select
    Selection.EntireRow.Insert
    ActiveCell.Offset(1, 0).Select
    End If
    Next i
    Application.ScreenUpdating = True

    End Sub
     
  4. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    Welcome to the board.

    "As you can tell from my example, the cells to be compared are alpha/numeric. Ideas?"

    Yes. If you try to sum alphanumeric values, you'll get zero. So use Subtotals, as follows:

    At each change in A801, use function Sum -- add subtotal to A801, uncheck "Summary below data".

    This will give you a formula below each group, e.g. =SUBTOTAL(9,B2:B3). You can then select the whole column, press F5, click Special, select Formulas, click OK, press Delete.

    HTH :)
     
  5. abgbpb

    abgbpb Thread Starter

    Joined:
    Dec 30, 2009
    Messages:
    3
    I understand completely.

    Thanks!
     
  6. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/889639

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice