How to selct half of cells from every filtered range?

[Takaim]

Hi,
I'm struggling with a problem that I cannot solve myself.

Every day I receive excel file with different amount of data:
Example:

Country+Place	Number	Country
DE-Place1	535632	DE
DE-Place2	2356	DE
DE-Place3	65867	DE
PL-Place1	7165	PL
PL-Place2	5768712	PL
PL-Place3	4354	PL
PL-Place4	87777	PL
US-Place1	725465	US
FR-Place1	353	FR
FR-Place2	344	FR
FR-Place3	345	FR
FR-Place4	346	FR
FR-Place5	347	FR
LU-Place1	987	LU
LU-Place2	988	LU

What I need to do is to select first half of cells with every country and to column D I need to add TEXT - 1, to second half - TEXT - 2, so it will looks like this.

Country+Place	Number	Country	TEXT
DE-Place1	535632	DE	TEXT - 1
DE-Place2	2356	DE	TEXT - 1
DE-Place3	65867	DE	TEXT - 2
PL-Place1	7165	PL	TEXT - 1
PL-Place2	5768712	PL	TEXT - 1
PL-Place3	4354	PL	TEXT - 2
PL-Place4	87777	PL	TEXT - 2
US-Place1	725465	US	TEXT - 1
FR-Place1	353	FR	TEXT - 1
FR-Place2	344	FR	TEXT - 1
FR-Place3	345	FR	TEXT - 1
FR-Place4	346	FR	TEXT - 2
FR-Place5	347	FR	TEXT - 2
LU-Place1	987	LU	TEXT - 1
LU-Place2	988	LU	TEXT - 2

I can do it manually but it takes too much time as in one file I can get even 300 rows of data.
I'm not very familiar with VBA, I tried to make so macro but it won't work as I wanted.

Please Login or Register  to view this content.

This code selct half of cells but when I filter data to see only DE country, it still select half of cells from ALL data (even hidden by filter).

There is another problem with data I receive, because every day I can get different countries.

Can I ask for help with this one?
Thank you

[MarvinP]

Hi Takaim and welcome to the forum,

Instead of VBA a simple formula might be what you need. After doing the formula you could copy the column and paste using Values Only to remove the formula and keep the numbers.

See the attached with a possible answer. I don't know what you mean by "first half" and "second half"...

Alternate by Country.xlsx

[Takaim]

Hi MarvinP,

Let me explain what I meant by "first half, second half".

If, for example, I get 10 rows with the PL country, I must assign the TEXT - 1 to the first five rows of PL and TEXT - 2 to rows 6-10. If I receive 7 rows with PL, then the first 4 rows must be have assigned TEXT - 1, and the remaining 3, TEXT - 2. (since the number of PL rows is odd). It need to be created this way :/

But now that I think about it...
You gave me almost everything I need for it. After using your formula I can just use Custom Sort. That will work.

EDIT:
Can I just ask for one more small change?

TEXT 1 and 2 are:
Alfa group
Beta group.

[Vraag en antwoord]

With a macro

Please Login or Register  to view this content.

[MarvinP]

Hi Takaim,

Use this formula in B2 of my example above to get Alpha and Beta

=CHOOSE(MOD(COUNTIF(A$1:A2,A2),2)+1,"Beta Group", "Alpha Group")