Inspiration and best method

[nparsons75]

Hi all,

I am really looking for inspiration on how best to do something. I have a system (table) that contains records, 1 record per row.

Each record (row) represents a project.

Each project will consist of a number of project members, in this case, a maximum of 8 members.

Currently I have to have 8 columns (member 1, member 2, member 3, and so on until member 8). This not only looks untidy due to the number of columns but it also makes it difficult to report on.

For example,

I would like to know, how many projects (rows) a particular member is associated with.

Each project has an ID number in the first column. I would like to know, which ID numbers a particular member is associated with.

Having the layout of members like this makes it difficult.

I really would like to maintain the 1 row per record as this seems to make sense, however I am baffled as how I can associate a number of memebrs with each one.

Inspiration and ideas all welcome.

Thank you so much in advance.

 

[MickG]

Perhaps something like this:-
Data:- Starting "A1"

ID number	Name	Name	Name	Name	Name	Name	Name	Name
I/D 1	Mem 1	Mem 2	Mem 3	Mem 4	Mem 5	Mem 6	Mem 7	Mem 8
I/D 2	Mem 2	Mem 3
I/D 3	Mem 8	Mem 9	Mem 10	Mem 11
I/D 4	Mem 4	Mem 5	Mem 6	Mem 7	Mem 8	Mem 9	Mem 10	Mem 11
I/D 5	Mem 10	Mem 11	Mem 12	Mem 13	Mem 14	Mem 15
I/D 6	Mem 1	Mem 2	Mem 3	Mem 4
I/D 7	Mem 2	Mem 3
I/D 8	Mem 1	Mem 2	Mem 3	Mem 4
I/D 9	Mem 10	Mem 11	Mem 12	Mem 13	Mem 14	Mem 15
I/D 10	Mem 9	Mem 10	Mem 11	Mem 12
I/D 11	Mem 8	Mem 9	Mem 10	Mem 11	Mem 12	Mem 13	Mem 14	Mem 15
I/D 12	Mem 2	Mem 3

<colgroup><col width="82" style="width: 62pt; mso-width-source: userset; mso-width-alt: 2929;"> <col width="122" style="width: 92pt; mso-width-source: userset; mso-width-alt: 4352;"> <col width="96" style="width: 72pt; mso-width-source: userset; mso-width-alt: 3413;"> <col width="104" style="width: 78pt; mso-width-source: userset; mso-width-alt: 3697;"> <col width="64" style="width: 48pt;" span="5"> <tbody> </tbody>

Results:- Starting "K1"

Mem Name	I/D's
Mem 1	I/D 1, I/D 6, I/D 8
Mem 2	I/D 1, I/D 2, I/D 6, I/D 7, I/D 8, I/D 12
Mem 3	I/D 1, I/D 2, I/D 6, I/D 7, I/D 8, I/D 12
Mem 4	I/D 1, I/D 4, I/D 6, I/D 8
Mem 5	I/D 1, I/D 4
Mem 6	I/D 1, I/D 4
Mem 7	I/D 1, I/D 4
Mem 8	I/D 1, I/D 3, I/D 4, I/D 11
Mem 9	I/D 3, I/D 4, I/D 10, I/D 11
Mem 10	I/D 3, I/D 4, I/D 5, I/D 9, I/D 10, I/D 11
Mem 11	I/D 3, I/D 4, I/D 5, I/D 9, I/D 10, I/D 11
Mem 12	I/D 5, I/D 9, I/D 10, I/D 11
Mem 13	I/D 5, I/D 9, I/D 11
Mem 14	I/D 5, I/D 9, I/D 11
Mem 15	I/D 5, I/D 9, I/D 11

<colgroup><col width="94" style="width: 71pt; mso-width-source: userset; mso-width-alt: 3356;"> <col width="64" style="width: 48pt;" span="4"> <tbody> </tbody>

Code:-

[COLOR="Navy"]Sub[/COLOR] MG07Sep51
[COLOR="Navy"]Dim[/COLOR] Ray [COLOR="Navy"]As[/COLOR] Variant, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
 Ray = ActiveSheet.Range("A1").CurrentRegion
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
    .CompareMode = vbTextCompare
    .Add "Mem Name", "I/D'[COLOR="Green"][B]s"[/B][/COLOR]
[COLOR="Navy"]For[/COLOR] n = 2 To UBound(Ray, 1)
    [COLOR="Navy"]For[/COLOR] Ac = 2 To UBound(Ray, 2)
      [COLOR="Navy"]If[/COLOR] Ray(n, Ac) <> "" [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]If[/COLOR] Not .Exists(Ray(n, Ac)) [COLOR="Navy"]Then[/COLOR]
            .Add Ray(n, Ac), Ray(n, 1)
        [COLOR="Navy"]Else[/COLOR]
            [COLOR="Navy"]If[/COLOR] InStr(.Item(Ray(n, Ac)), Ray(n, Ac)) = 0 [COLOR="Navy"]Then[/COLOR]
                .Item(Ray(n, Ac)) = .Item(Ray(n, Ac)) & ", " & Ray(n, 1)
            [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]End[/COLOR] If
     [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Ac
[COLOR="Navy"]Next[/COLOR] n
Range("K1").Resize(.Count, 2) = Application.Transpose(Array(.Keys, .items))
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[nparsons75]

Hey, thank you, looking like a great start to improving what I have. I have added the code to the worksheet but it hasn't done anything, how do I activate it? and could i do this on another worksheet in a report format as opposed to next to my columns which exist.

Thank you.

 

[nparsons75]

Hi, I have tried now to run it as a macro. When I did i got an error, 13, type mismatch

 

[MickG]

Can you post an example of data and expected results.