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 shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
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

SandsB
  • Question Question
Pivot Table showing just averages
Replies
4
Views
110
SandsB
SandsB
O
  • Solved
What Is Best Practice To Facilitate Column Layout Change In A Project?
Replies
2
Views
205
ozex
O
M
  • Question Question
Pivot table and graph for member data for a non-profit
Replies
0
Views
129
Manexcel
M
M
  • Solved
VBA: Checking if a row exists with exact data in multiple columns
Replies
6
Views
723
mwl_y
M
J
  • Question Question
Group last name based on range in alphabet
Replies
2
Views
149
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,711
Messages
6,174,029
Members
452,542
Latest member
Bricklin

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