+ Reply to Thread
Results 1 to 5 of 5

Checking every 2 rows, finding the number a cell ends with, and grouping

  1. #1
    Registered User
    Join Date
    01-22-2013
    Location
    Austin, Texas
    MS-Off Ver
    Excel 2010
    Posts
    14

    Checking every 2 rows, finding the number a cell ends with, and grouping

    First, thanks for any help you can provide...
    This is tricky for me, so any advice or direction will help a great deal in automating this procedure.


    I have thousands of rows of data.

    Every 2 rows produce one set of information. (IE Row 11 and row 12 is one person's data. Row 13 and 14 is another person)

    Let's say rows 11 through 18 produce data for 4 people all in the same building.

    Then rows 19 through 24 produce data for 3 people, all in another building.

    The 1st cell of the 2nd row identifies the person's number, and therefore how many people are in the building.
    (ie: cell A12 = 02211, then cell A14 = 02212, A16= 02213, A18= 02214, then A20 = 02221)

    Since the last number of A18 is "4" we know that previous 4 people were all in the same building, and starting with A19, we have a new group of people in a new building.


    I need to a formula that will run down and check ONLY the last number of that cell, to tell me how many people are in the building, and produce the number for the previous pairs.

    So we know A11-A18 produces data for 4 people in one building. Let's put the results in V12,V14, V16 and V18 (they would all equal 4).

    Does this make sense?
    Thoughts?

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,123

    Re: Checking every 2 rows, finding the number a cell ends with, and grouping

    perhaps it would be easier to help you if you uploaded a sample workbook, showing what data you are working with, a few examples of what your expected outcome would be, and how you would arrive at that (remove any confidential info if necessary).
    To attach a file to your post,
    click advanced (next to quick post),
    scroll down until you see "manage file",
    click that and select "add files" (top right corner).
    click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    01-22-2013
    Location
    Austin, Texas
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: Checking every 2 rows, finding the number a cell ends with, and grouping

    sample.xlsx

    This work?

  4. #4
    Registered User
    Join Date
    01-22-2013
    Location
    Austin, Texas
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: Checking every 2 rows, finding the number a cell ends with, and grouping

    If you look at the sample, A1 through A8 are in the same building (let's call it building 1-0221)

    Starting with A9, the person is in a new building, with only 3 people to that building.


    Think of this spreadsheet with thousands of people, all going down.

    The end result is, I need to calculate the number of people per building, multiply it by each person's "E2" cell, then subtract from each person's E1 cell, then divide that by the number of people in the building.

    So for the person in rows 1&2, the formula would result in $14.41
    (35*4 = 140 - 82.35 = 57.65 / 4 = $14.41)

    This would be a formula for EACH PERSON.

    Hope not too confusing...

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,123

    Re: Checking every 2 rows, finding the number a cell ends with, and grouping

    Try this, copied down...
    =IF(B1="",0,(COUNTIF(A:A,A1)*E2)-E1)/(COUNTIF(A:A,A1))

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1