Complex listing of names

[trademark2k6]

Hi All,

New to the forum but have had loads of help from reading all the articles in the past. I have a tricky task which I am trying to achieve with excel and not sure if it is possible.

I have a table that is laid out like below (with a couple rows completed).

I want to be able to generate a list like this:

List of authors with their affiliations: Mark T [1][2][3], Smith P [4], Cruise K [2]

Separately, I want a list of all the selected affiliations and the assigned number:

• [1] BRC
• [2] Group 1
• [3] UHB
• [4] Group 2

As you can see Author 1 (Mark T) has 1,2,3 marked against his name.

Smith P also has UoB listed like Mark T, but because t is a different group his name is listed as Smith P [4]

If any author selects the same affiliation, they share the same number. E.g. Cruise K is also in Group 1 of UoB, which is the same as Mark T. Therefore both will have the number [2] next to their names.

Each affiliation should be numbered according to first. So because Mark T has selected BRC as Yes, BRC is numbered as [1] and if anyone else selects the same, they will also have [1] next to their name.

Complicated, but hoping someone has some tricks to make this work!

Thanks


[TABLE="class: outer_border, width: 1164"]
<tbody>[TR]
[TD]Author Information
[/TD]
[TD="align: center"]Name (eg. Smith J)
[/TD]
[TD="align: center"]Affiliation:
BRC (Y/N)
[/TD]
[TD="align: center"]Affiliation:
UoB (Group 1,2,3)
[/TD]
[TD="align: center"]Affiliation:
UHB (Y/N)
[/TD]
[/TR]
[TR]
[TD]Author 1
[/TD]
[TD]Mark T
[/TD]
[TD]Yes
[/TD]
[TD]Group 1
[/TD]
[TD]Yes
[/TD]
[/TR]
[TR]
[TD]Author 2
[/TD]
[TD]Smith P
[/TD]
[TD] No
[/TD]
[TD]Group 2
[/TD]
[TD]No
[/TD]
[/TR]
[TR]
[TD]Author 3
[/TD]
[TD]Cruise K
[/TD]
[TD] No
[/TD]
[TD]Group 1
[/TD]
[TD]No
[/TD]
[/TR]
[TR]
[TD]Author 4
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Author 5
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[hiker95]

trademark2k6,

Welcome to the MrExcel forum.

I am not able to tell what worksheet, and, range, the raw data is in, and, I am not able to tell what worksheet, and, range the results should be in.


You can post your workbook/worksheets to the following free site (sensitive data changed), mark the workbook for sharing, and, provide us with a link to your workbook:

https://dropbox.com

 

[trademark2k6]

hiker95,

Here you go. I added some text box to explain where to enter data. Hopefully this is possible!

https://www.dropbox.com/s/khafot8qqatwq73/mrexcel.xlsx?dl=0

Thanks

 

[hiker95]

hiker95,

Here you go. I added some text box to explain where to enter data. Hopefully this is possible!

https://www.dropbox.com/s/khafot8qqatwq73/mrexcel.xlsx?dl=0

Thanks

trademark2k6,

Thanks for the workbook/worksheets.

I have examined your workbook, but, I do not understand what you are trying to do.

Maybe someone else on MrExcel will be able to help you.