[RESOLVED] Reading a column of data and summarising the data based on codes

[scoobster247]

Hi Forum

Please see the data example below. This is a small block of data for 2 countries (Belgium and Austria). Within these these are zip codes and zip groups (the first 2 characters of the zip code), and the name of the team working within the zip. There can be one or more teams working within a zip group.

What I need is to summarize the data so that I get the lowest and highest zip group for a team within a country. If there is more than one team working within a zip group then I need the lowest and highest zips. If these is only one team working in a country then I just need the lowest and highest but also a comment to say only one team for that country.

The lowest and highest values I need are from reading the file from start to finish. By that I mean I don't need the highest value for that team within the entire block of data.0

If there is only one 'team' for a zip group then I want to output the lowest and highest consecutive zip group for that team.
If there are more than one 'team' for a zip group then I want to output the lowest and highest consecutive zip for that team.
If within a country there is only one 'team' then I want to output the lowest and highest consecutive zip group for that team and add a comment of 'complete "country"'

The expected output based on the source data is shown at the bottom of the image.

Is it possible?

Many thanks for your help.

[scoobster247]

updated the attachment.

[scoobster247]

Sorry, having reread my original post I should have been clearer in that I have a larger data set than that shown so would ideally like to use a VB solution.

Any thoughts or comments on if it's possible?

Many thanks.

[vbfbryce]

Surely possible, but I'm not real clear on what you're trying to do. Maybe show us the starting point as well as the end point (which you've given us), and list the steps a bit more clearly.

[scoobster247]

Thanks for coming back on this. And good to know it's possible.

The starting point is the data shown at the top of the image. So 4 columns containing zip code assignment within a country.

The steps as I see it are;
1) Look at all the teams for a single country and if only one is shown per country then output the first zip grouping and last zip grouping for that team. Also output that it's for the entire country
So in the example I would expect to see for country AT the value 11 (lowest zip grouping) and 14 (highest zip grouping). The same can't be done for country BE as there are multiple teams.

2) Now there must be more than one team for a single country so check for each zip grouping to see if there is only one team within the grouping and if so, we need to output the
zip grouping (lowest and highest) for each team.
Using the example I would expect to see for country BE the output of zip grouping for Bob, Harry and Jan because there is no other team member within their zip groupings
The output for Bob would be 10 (lowest zip grouping) and 10 (highest (and only) zip grouping) because there is not another team for that grouping
The output for Harry would be 12 (lowest zip grouping) and 14 (highest and only zip grouping) because there is not another team for that grouping
The output for Jan would be 15 (lowest zip grouping) and 15 (highest and only zip grouping) because there is not another team for that grouping

3) If there is more than one team within a zip grouping then the same lowest / highest output to apply but it needs to be the zip instead of the zip grouping
So for zip grouping 11 there are several teams (Mike Bob Harry Dick). Therefore, we need to output the lowest zip and highest zip for these 4 teams
The output for Mike would be 1142 and 1142 because that is the only zip range for Mike with zip grouping 11
The output for Bob would be 1155 and 1157 because that is the lowest and highest zip range for Bob with zip grouping 11
The output for Harry would be 1190 and 1190 because that is the only zip range for Harry with zip grouping 11
The output for Dick would be 1195 and 1195 because that is the only zip range for Dick with zip grouping 11

Note that a team can have more than one zip grouping but the processing is required to run top to top on the ordered list.

Hope this helps to make it clearer. I have worksheets with over 16k records to process so some kind of VBA program is going to be ideal, if possible.

Thanks again.

[vbfbryce]

That helps, will check it out.

[scoobster247]

Many thanks for taking the time to reply and for also looking at this puzzle.

[scoobster247]

good morning vbfbryce. Please let me know if anything needs further explanation from the summary I posted. Thanks again for anything you can offer on this.

[vbfbryce]

nothing further necessary, just got back to the office today. will get after it, since I don't feel like doing my "real work!"

[scoobster247]

Thanks so much.

Just one other comment to make is that there could be another 'team' to the right of the last team shown in the example. The name in the column could have the same zip code and grouping but not always. So ideally once the processing for 'team' is written out then the same logic should be applied for the next 'team' column. If that makes sense?

[vbfbryce]

I have become less clear again...

What would the output of the attached data look like?

[scoobster247]

Hi

The desired output is shown in the bottom section of the Excel screen image I posted, based on the sample data (Shown on the top section of the image). So it's the part that has 'Start/End/Team/Comment'

[scoobster247]

Sorry, I hit submit before I finished typing ...

So the key points are that is a team is the only team in a country then we just output the country rather than individual zips. And if there are more than one team within a zip grouping then we have to output the zip instead of the zip groupings (it's a lower level of details and avoids the same team having the same zip group within a country.

[vbfbryce]

What confuses me is that there is no reference to Country in your output section at the bottom, yet it appears that it is your "first sort."

[scoobster247]

10 11 Bob
12 14 harry
15 15 jan
11 11 mike
16 16 tom
11 14 tom "Complete AT"

But in your example there is only one team within a zip group. So if you look at my original example you can see that with 'BE' and zip grouping '11' there are 4 different teams so the output is using 'zip instead of 'zip group'

[scoobster247]

The source data needs to be sorted on country so that we keep the zips within that country together because the processing is reading the source data top to bottom.

We could output country to the extract file. Then I could see the country that each output record is for. Could be useful. I think I just included the miminal fields to keep the example as small as possible when I first posted.

[vbfbryce]

Step through the small amount of code in the attached. It handles the "one team per country" scenario, but I'm not sure how to handle more than one team per country exactly.

[scoobster247]

Many thanks for the code. I can take that forward to meet my needs. Much appreciated.