Inspiration and best method

N

nparsons75

Well-known Member
Joined
Sep 23, 2013
Messages
1,256
Office Version
  1. 2016
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.
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Sort by date Sort by votes
Perhaps something like this:-
Data:- Starting "A1"
[TABLE="width: 544"]
<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>[TR]
[TD="class: xl63, width: 82, bgcolor: transparent"]ID number[/TD]
[TD="class: xl63, width: 122, bgcolor: transparent"]Name[/TD]
[TD="class: xl63, width: 96, bgcolor: transparent"]Name[/TD]
[TD="class: xl63, width: 104, bgcolor: transparent"]Name[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"]Name[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"]Name[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"]Name[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"]Name[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"]Name[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]I/D 1[/TD]
[TD="class: xl63, bgcolor: transparent"]Mem 1[/TD]
[TD="class: xl63, bgcolor: transparent"]Mem 2[/TD]
[TD="class: xl63, bgcolor: transparent"]Mem 3[/TD]
[TD="class: xl63, bgcolor: transparent"]Mem 4[/TD]
[TD="class: xl63, bgcolor: transparent"]Mem 5[/TD]
[TD="class: xl63, bgcolor: transparent"]Mem 6[/TD]
[TD="class: xl63, bgcolor: transparent"]Mem 7[/TD]
[TD="class: xl63, bgcolor: transparent"]Mem 8[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]I/D 2[/TD]
[TD="class: xl63, bgcolor: transparent"]Mem 2[/TD]
[TD="class: xl63, bgcolor: transparent"]Mem 3[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]I/D 3[/TD]
[TD="class: xl63, bgcolor: transparent"]Mem 8[/TD]
[TD="class: xl63, bgcolor: transparent"]Mem 9[/TD]
[TD="class: xl63, bgcolor: transparent"]Mem 10[/TD]
[TD="class: xl63, bgcolor: transparent"]Mem 11[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]I/D 4[/TD]
[TD="class: xl63, bgcolor: transparent"]Mem 4[/TD]
[TD="class: xl63, bgcolor: transparent"]Mem 5[/TD]
[TD="class: xl63, bgcolor: transparent"]Mem 6[/TD]
[TD="class: xl63, bgcolor: transparent"]Mem 7[/TD]
[TD="class: xl63, bgcolor: transparent"]Mem 8[/TD]
[TD="class: xl63, bgcolor: transparent"]Mem 9[/TD]
[TD="class: xl63, bgcolor: transparent"]Mem 10[/TD]
[TD="class: xl63, bgcolor: transparent"]Mem 11[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]I/D 5[/TD]
[TD="class: xl63, bgcolor: transparent"]Mem 10[/TD]
[TD="class: xl63, bgcolor: transparent"]Mem 11[/TD]
[TD="class: xl63, bgcolor: transparent"]Mem 12[/TD]
[TD="class: xl63, bgcolor: transparent"]Mem 13[/TD]
[TD="class: xl63, bgcolor: transparent"]Mem 14[/TD]
[TD="class: xl63, bgcolor: transparent"]Mem 15[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]I/D 6[/TD]
[TD="class: xl63, bgcolor: transparent"]Mem 1[/TD]
[TD="class: xl63, bgcolor: transparent"]Mem 2[/TD]
[TD="class: xl63, bgcolor: transparent"]Mem 3[/TD]
[TD="class: xl63, bgcolor: transparent"]Mem 4[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]I/D 7[/TD]
[TD="class: xl63, bgcolor: transparent"]Mem 2[/TD]
[TD="class: xl63, bgcolor: transparent"]Mem 3[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]I/D 8[/TD]
[TD="class: xl63, bgcolor: transparent"]Mem 1[/TD]
[TD="class: xl63, bgcolor: transparent"]Mem 2[/TD]
[TD="class: xl63, bgcolor: transparent"]Mem 3[/TD]
[TD="class: xl63, bgcolor: transparent"]Mem 4[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]I/D 9[/TD]
[TD="class: xl63, bgcolor: transparent"]Mem 10[/TD]
[TD="class: xl63, bgcolor: transparent"]Mem 11[/TD]
[TD="class: xl63, bgcolor: transparent"]Mem 12[/TD]
[TD="class: xl63, bgcolor: transparent"]Mem 13[/TD]
[TD="class: xl63, bgcolor: transparent"]Mem 14[/TD]
[TD="class: xl63, bgcolor: transparent"]Mem 15[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]I/D 10[/TD]
[TD="class: xl63, bgcolor: transparent"]Mem 9[/TD]
[TD="class: xl63, bgcolor: transparent"]Mem 10[/TD]
[TD="class: xl63, bgcolor: transparent"]Mem 11[/TD]
[TD="class: xl63, bgcolor: transparent"]Mem 12[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]I/D 11[/TD]
[TD="class: xl63, bgcolor: transparent"]Mem 8[/TD]
[TD="class: xl63, bgcolor: transparent"]Mem 9[/TD]
[TD="class: xl63, bgcolor: transparent"]Mem 10[/TD]
[TD="class: xl63, bgcolor: transparent"]Mem 11[/TD]
[TD="class: xl63, bgcolor: transparent"]Mem 12[/TD]
[TD="class: xl63, bgcolor: transparent"]Mem 13[/TD]
[TD="class: xl63, bgcolor: transparent"]Mem 14[/TD]
[TD="class: xl63, bgcolor: transparent"]Mem 15[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]I/D 12[/TD]
[TD="class: xl63, bgcolor: transparent"]Mem 2[/TD]
[TD="class: xl63, bgcolor: transparent"]Mem 3[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[/TR]
</tbody>[/TABLE]

Results:- Starting "K1"
[TABLE="width: 263"]
<colgroup><col width="94" style="width: 71pt; mso-width-source: userset; mso-width-alt: 3356;"> <col width="64" style="width: 48pt;" span="4"> <tbody>[TR]
[TD="class: xl65, width: 94, bgcolor: transparent"]Mem Name[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]I/D's[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Mem 1[/TD]
[TD="class: xl65, bgcolor: transparent"]I/D 1, I/D 6, I/D 8[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Mem 2[/TD]
[TD="class: xl65, bgcolor: transparent"]I/D 1, I/D 2, I/D 6, I/D 7, I/D 8, I/D 12[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Mem 3[/TD]
[TD="class: xl65, bgcolor: transparent"]I/D 1, I/D 2, I/D 6, I/D 7, I/D 8, I/D 12[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Mem 4[/TD]
[TD="class: xl65, bgcolor: transparent"]I/D 1, I/D 4, I/D 6, I/D 8[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Mem 5[/TD]
[TD="class: xl65, bgcolor: transparent"]I/D 1, I/D 4[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Mem 6[/TD]
[TD="class: xl65, bgcolor: transparent"]I/D 1, I/D 4[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Mem 7[/TD]
[TD="class: xl65, bgcolor: transparent"]I/D 1, I/D 4[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Mem 8[/TD]
[TD="class: xl65, bgcolor: transparent"]I/D 1, I/D 3, I/D 4, I/D 11[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Mem 9[/TD]
[TD="class: xl65, bgcolor: transparent"]I/D 3, I/D 4, I/D 10, I/D 11[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Mem 10[/TD]
[TD="class: xl65, bgcolor: transparent"]I/D 3, I/D 4, I/D 5, I/D 9, I/D 10, I/D 11[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Mem 11[/TD]
[TD="class: xl65, bgcolor: transparent"]I/D 3, I/D 4, I/D 5, I/D 9, I/D 10, I/D 11[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Mem 12[/TD]
[TD="class: xl65, bgcolor: transparent"]I/D 5, I/D 9, I/D 10, I/D 11[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Mem 13[/TD]
[TD="class: xl65, bgcolor: transparent"]I/D 5, I/D 9, I/D 11[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Mem 14[/TD]
[TD="class: xl65, bgcolor: transparent"]I/D 5, I/D 9, I/D 11[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Mem 15[/TD]
[TD="class: xl65, bgcolor: transparent"]I/D 5, I/D 9, I/D 11[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
</tbody>[/TABLE]



Code:-
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
 
Upvote 0
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.
 
Upvote 0
Hi, I have tried now to run it as a macro. When I did i got an error, 13, type mismatch
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
Project Count per Team Member Formula Needed
Replies
1
Views
75
Eric W
Eric W
W
  • Question Question
Formatting raw data with extra rows automatically added in-between records
Replies
1
Views
76
Peter_SSs
Peter_SSs
SandsB
  • Question Question
Pivot Table showing just averages
Replies
4
Views
149
SandsB
SandsB
O
  • Solved
What Is Best Practice To Facilitate Column Layout Change In A Project?
Replies
2
Views
215
ozex
O
M
  • Question Question
Pivot table and graph for member data for a non-profit
Replies
0
Views
159
Manexcel
M

Forum statistics

Threads
1,224,744
Messages
6,180,697
Members
452,994
Latest member
Janick

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top