Creating a macro

[Guest]

Hi,

I have another macro I need creating and I'm hoping the experts out there
are able to help me - because I'm certainly not one of them!?

I have 10,000 rows of data which I need to sort. Basically in A1 I have the
wording "Searching Group :" and in B1 I have the name of the Group with a
prefix of "CN=" and then the actual name of the group. Then in the rows below
there is details of the members of this group. These are identified because
in A2 it says "Member:" and across row 2 it gives details of that member.

In fact I don't need any of this detail, I just actually need to calculate
the number of members the group has. After a random number of rows -
depending on the number of members of this group - Column A then has a row
that says "Searching Group :" again, with the corresponding name detail in
Column B of that Row beginning with "CN=".

What I actually need is:
Column A = Group Name ***(without the CN= prefix)*** (the Group Name (which
currently has the CN= prefix) is currently in Column B)
Column B = Number of Members of that group (i.e. the number of rows below
the "Searching Group" row until the next row that says "Searching Group"
instead of "Member:"

I hope this makes sense! Can anyone help?

Thanks,
Emily

 

[wdk]

Hi,

I have another macro I need creating and I'm hoping the experts out there
are able to help me - because I'm certainly not one of them!?

I have 10,000 rows of data which I need to sort. Basically in A1 I have the
wording "Searching Group :" and in B1 I have the name of the Group with a
prefix of "CN=" and then the actual name of the group. Then in the rows below
there is details of the members of this group. These are identified because
in A2 it says "Member:" and across row 2 it gives details of that member.

In fact I don't need any of this detail, I just actually need to calculate
the number of members the group has. After a random number of rows -
depending on the number of members of this group - Column A then has a row
that says "Searching Group :" again, with the corresponding name detail in
Column B of that Row beginning with "CN=".

What I actually need is:
Column A = Group Name ***(without the CN= prefix)*** (the Group Name (which
currently has the CN= prefix) is currently in Column B)
Column B = Number of Members of that group (i.e. the number of rows below
the "Searching Group" row until the next row that says "Searching Group"
instead of "Member:"

I hope this makes sense! Can anyone help?

Thanks,
Emily

Are you familiar with Pivot Tables? I believe you can accomplish what
you want by using that option..

 

[Guest]

Hi,

thanks for your feedback. I know very little about pivot tables, so I guess
I'll need to learn! I thought that a macro was probably the quickest and
easiest option - especially given that I want to remove the prefix from one
field. I have actually been able to do what I want, but it has been messy
with duplicate sheets and formulas etc and quite time consuming. I now have a
new set of data that I need to go through the process with, so I'll see what
I can find re pivot tables. If you have any suggestions as to the best place
for a crash course I would appreciate it!

Thanks,
Emily

 

[wdk]

Hi,

thanks for your feedback. I know very little about pivot tables, so I guess
I'll need to learn! I thought that a macro was probably the quickest and
easiest option - especially given that I want to remove the prefix from one
field. I have actually been able to do what I want, but it has been messy
with duplicate sheets and formulas etc and quite time consuming. I now have a
new set of data that I need to go through the process with, so I'll see what
I can find re pivot tables. If you have any suggestions as to the best place
for a crash course I would appreciate it!

Thanks,
Emily






- Show quoted text -

They are hard to explain (for me) but once you get the hang of them,
they become very handy. Try searching for Pivot Table in Google and
you'll get loads of sites. Chip Pearson and others have excellent
places to start. As far as stripping prefixes and you aren't
comfortable with some the function commands, a handy utility from ASAP
Utilities will handle this. Again there are other excellent utilities
out there that will do this, too.

 

[maxsgroi]

They are hard to explain (for me) but once you get the hang of them,
they become very handy. Try searching for Pivot Table in Google and
you'll get loads of sites. Chip Pearson and others have excellent
places to start. As far as stripping prefixes and you aren't
comfortable with some the function commands, a handy utility from ASAP
Utilities will handle this. Again there are other excellent utilities
out there that will do this, too.

You could insert two columns on the left of your data (so that your
first "Searching group:" will be display in C1) and:

A1=IF(C1="Searching group:";RIGHT(D1;3);"Member:") and copy it down

B1=IF(C1="Searching group:";H1;D1) and copy it down

In column C, D, E, F your data (I suppose your data spread over 4
columns, if your data spread out over more than 4 columns you need
only little changes)

G1=A1
G2=IF(C2<>"Member:";A2;G1) and copy it down

H1=IF(C1="Searching group:";COUNTIF(G1:G12;A1)-1;"")

Than you can:
1) hide columns C and D

or

2) copy A1:F1000 and Past Special (values) on another sheet and delete
columns you are not interested in

Hope it helps
Max

 

[maxsgroi]

You could insert two columns on the left of your data (so that your
first "Searching group:" will be display in C1) and:

A1=IF(C1="Searching group:";RIGHT(D1;3);"Member:") and copy it down

Sorry.. A1 it's wrong, the right one:
A1=IF(C1="Searching group:";MID(D1;4;LEN(D1)-3);"Member:") and copy
it down

B1=IF(C1="Searching group:";H1;D1) and copy it down

In column C, D, E, F your data (I suppose your data spread over 4
columns, if your data spread out over more than 4 columns you need
only little changes)

G1=A1
G2=IF(C2<>"Member:";A2;G1) and copy it down

H1=IF(C1="Searching group:";COUNTIF(G1:G12;A1)-1;"")

Than you can:
1) hide columns C and D

or

2) copy A1:F1000 and Past Special (values) on another sheet and delete
columns you are not interested in

Regards
Max

 

[Guest]

Hi Max,

Thanks for that. Have been away for a few days, bt looks like exactly what
I'm after - thanks again!

Emily