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

**ninj5** · 01-22-2013, 02:19 PM

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?

**FDibbins** · 01-22-2013, 03:54 PM

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.

**ninj5** · 01-22-2013, 04:07 PM

sample.xlsx

This work?

**ninj5** · 01-22-2013, 04:14 PM

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