Transpose data with multiple criteria

[ralsnz]

Hi all

I am facing a challenge:

I have few rooms with multiples equipment inside. For each equipment I need to answer 9 questions, there is only 3 option: red, orange or green.

The list with all the equipment by room are built as bellow:

Room 1	Room 2	Room 3
Equipment X	Equipment Y	Equipment Z
Equipment XZ	Eq YX	Eq ZX
Eq ZY	Eq XZY	Eq ZXY

<tbody>
</tbody>

In a different sheet I have this table with the answers:

Equipment	Question 1	Q2	Q3	Q4	Q5	Q6	Q7	Q8	Q9
X	Red	R	R	Orange	Green	G	G	G	G
Y	R	R	R	R	O	O	G	G	G
Z	G	G	R	R	O	O	G	G	G
XZ	G	G	G	G	G	G	G	R	G
YX	G	R	G	G	G	G	G	G	O
ZX	G	G	G	G	G	G	G	G	G
ZY	R	R	G	G	G	G	G	G	G
XZY	O	O	G	G	G	G	G	G	G
ZXY	O	O	O	G	G	G	G	G	G

<tbody>
</tbody>

To be able to built a pivot table, I need to present the data like:

Room	Equipment	Answers in Red	Answers in Orange
Room1	X	R
Room1	X	R
Room1	X		O
Room1	XZ	R
Room1	ZY	R
Room1	ZY	R
Room2	Y	R
Room2	Y	R
Room2	Y	R
Room2	Y	R
Room2	Y		O
Room2	Y		O

<tbody>
</tbody>

And so on. The only problem that my database is big as ****. So I need to generate a VBA for it.

Someone could give me a light on this?


Cheers.







****** id="cke_pastebin" style="position: absolute; top: 606.4px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">

O

<tbody>
</tbody>

 

[mse330]

Welcome to Mr. Excel ralsnz !

Can you show us the a sample of the expected results after running the macro ? Are you trying to end up with the 3rd table you posted ? If so, equipment x is only repeated 3 times whereas it should be shown 9 times for each answer, or I am misunderstanding your request ? Could you please elaborate a little more

 

[ralsnz]

Hello mse330,

Sorry for the lack of information. Yes, I'm trying to end up with table 3, although I just want to show the equipment with red and orange answers.
The main problem is base in two major tables, as bellow (tab 1 and tab 2). Table 3 is expected one, but I need to fill the room column.

Table 1:

rooms

room 1

room 2

room 3

room 4

room 5

room 6

room 7

room 8

room 9

room 10

room 11

room 12

DIS-02

MIX-02

DRY-03

MIB-02

GRN-10

SCL-114

SCL-69

SCL-103

SCL-69

TCP-02

TCP-05

TCP-11

SCL-100

MIX-05

DRY-05

SIV-02

SCL-19

MIL-03

SCL-105

MIS-25

TDD-10

TDD-12

TDD-17

SCL-83

SCL-91

SIV-01

CPS-02

INV-01

SCL-106

MIS-18

TMC-07

TMC-16

TMC-17

SCL-17

SCL-98

MIL-07

CPS-03

SIV-08

HRD-11

TCT-01

TCP-13

HRD-14

HRD-13

MIS-20

CSM-02

GRN-01

DIS-06

TDD-11

DIS-09

DRY-07

TMC-09

DRY-01

FRI-07

HRD-12

DRY-13

MIS-34

SCL-93

SCL-90

VAC-16

SCL-90

PMP-02

FRI-04

SCL-99

SCL-67

DIS-05

SCL-89

ENC-06

SCL-102

PMP-25

CEL-01

SCL-11

CLM-01

SCL-88

CSM-01

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

Table 2:

Equipment	question 2	question 3	question 4	question 5	question 6	question 7	question 8	question 9	question 10
SCL-17	green	green	orange	red	red	red	green	red	red
SCL-83	green	green	orange	red	red	red	green	red	red
SCL-100	green	green	orange	red	red	red	green	red	red
MIS-18	green	green	orange	red	green	red	red	red	green
MIS-20	green	green	orange	red	green	red	red	red	green
MIS-34	green	green	orange	red	green	red	red	red	green
GRN-10	green	green	orange	orange	green	green	red	green	green
GRN-01	red	green	red	orange	red	red	green	green	green
MIL-07	green	green	green	orange	red	orange	red	green	green
MIL-03	red	green	red	orange	red	orange	red	green	green
SIV-01	green	green	orange	orange	red	orange	red	green	green
SIV-02	green	green	orange	orange	red	orange	red	green	green
SIV-08	green	green	orange	orange	red	orange	red	green	green
MIX-02	green	green	orange	red	orange	orange	red	green	green
MIX-05	green	green	orange	red	orange	orange	red	green	green
DRY-03	green	green	orange	orange	green	orange	red	green	green
DRY-05	green	green	orange	orange	green	orange	red	green	green
DRY-07	green	green	orange	orange	red	orange	red	green	green
DRY-13	green	green	orange	orange	red	orange	red	green	green
DRY-01	red	green	red	orange	red	red	green	green	green
MIB-02	red	green	red	red	red	orange	red	green	green
SCL-99	green	green	orange	red	red	red	green	red	red
SCL-102	green	green	orange	red	red	red	green	red	red
SCL-103	green	green	orange	red	red	red	green	red	red
SCL-114	green	green	orange	red	red	red	green	red	red
SCL-69	green	green	orange	red	red	red	green	red	red
SCL-93	green	green	orange	red	red	red	green	red	red
SCL-98	green	green	orange	red	red	red	green	red	red
SCL-88	green	green	orange	red	red	red	green	red	red
SCL-91	green	green	orange	red	red	red	green	red	red
SCL-106	green	green	orange	red	red	red	green	red	red
SCL-11	green	green	orange	red	red	red	green	red	red
SCL-89	green	green	orange	red	red	red	green	red	red
TCP-13	green	green	orange	red	red	orange	red	orange	green
TCP-11	green	green	orange	red	red	orange	red	orange	green
TCP-05	green	green	orange	orange	red	orange	red	green	green
TCP-02	green	green	orange	orange	red	orange	red	green	green
TMC-07	green	green	orange	red	red	red	green	red	green
TMC-16	green	green	orange	red	red	red	green	red	green
TMC-17	green	green	orange	red	red	red	green	red	green
TDD-17	green	green	orange	red	red	orange	red	green	green
TDD-10	green	green	orange	red	red	orange	red	green	green
TDD-12	green	green	orange	red	red	orange	red	green	green
VAC-16	green	green	orange	green	green	green	red	green	red
ENC-06	green	red	orange	orange	red	red	green	orange	green
TMC-09	green	green	orange	red	red	red	green	red	green
CLM-01	green	green	orange	red	red	red	green	orange	green
CSM-01	green	green	orange	red	red	red	green	orange	green
CSM-02	green	green	orange	red	red	red	green	orange	green
CEL-01	green	green	red	red	red	red	green	orange	green
CPS-02	green	green	orange	red	red	red	green	orange	green
CPS-03	green	green	orange	red	red	red	green	orange	green
SCL-19	green	green	orange	red	red	red	green	red	red
SCL-90	green	green	orange	red	red	red	green	red	red
MIS-20	green	green	orange	red	green	red	red	red	green
MIS-18	green	green	orange	red	green	red	red	red	green
TCT-01	red	green	red	red	red	orange	red	green	green
INV-01	green	green	red	red	red	orange	red	green	green
DIS-02	green	green	red	red	orange	orange	red	green	green
PMP-02	green	green	red	red	orange	orange	red	green	green
PMP-25	green	green	red	red	orange	orange	red	green	green
FRI-07	green	green	red	red	red	green	red	green	green
FRI-04	green	green	red	red	red	green	red	green	green
HRD-12	green	red	red	red	red	green	red	green	green
TDD-11	green	green	red	red	red	green	red	green	green
DIS-09	green	green	red	orange	red	green	red	green	green
DIS-05	green	green	red	orange	red	green	red	green	green
HRD-13	green	green	red	red	red	green	red	green	green
HRD-11	green	green	red	red	red	green	red	green	green
HRD-14	green	green	red	red	red	green	red	green	green
SCL-67	green	green	orange	red	red	red	green	red	red
SCL-105	green	green	orange	red	red	red	green	red	red
DIS-06	green	green	red	orange	red	green	red	green	green
MIS-25	green	green	orange	red	green	red	red	red	green

<colgroup><col><col><col span="2"><col><col span="3"><col><col></colgroup><tbody>
</tbody>

Table 3: Table expected as result

Equipment number	Equipment name list	Questions	Answers	Room
SCL-17	Balance	question 1	green
SCL-83	Balance	question 1	green
SCL-100	Balance	question 1	green
MIS-18	Overhead Stirrer	question 1	green
MIS-20	Overhead Stirrer	question 1	green
MIS-34	Overhead Stirrer	question 1	green
GRN-10	Bear Mixer	question 1	green
GRN-01	VG 400 Granulator	question 1	red
MIL-07	Fitzmill	question 1	green
MIL-03	Comil	question 1	red
SIV-01	Russel Sieve	question 1	green
SIV-02	Russel Sieve	question 1	green
SIV-08	Russel Sieve	question 1	green
MIX-02	Eccentric Drum Roller	question 1	green
MIX-05	Eccentric Drum Roller	question 1	green
DRY-03	Dry Oven	question 1	green
DRY-05	Dry Oven	question 1	green
DRY-07	Dry Oven	question 1	green
DRY-13	Dry Oven	question 1	green
DRY-01	Fluid bed dryer	question 1	red
MIB-02	Double cone blender	question 1	red
SCL-99	Balance	question 1	green
SCL-102	Balance	question 1	green
SCL-103	Balance	question 1	green
SCL-114	Balance	question 1	green
SCL-69	Moisture Analyser	question 1	green
SCL-93	Moisture Analyser	question 1	green
SCL-98	Moisture Analyser	question 1	green
SCL-88	Moisture Analyser	question 1	green
SCL-91	Moisture Analyser	question 1	green
SCL-106	Balance	question 1	green
SCL-11	Balance	question 1	green
SCL-89	Balance	question 1	green
TCP-13	Compression Machine	question 1	green
TCP-11	Compression Machine	question 1	green
TCP-05	Compression Machine	question 1	green
TCP-02	Compression Machine	question 1	green
TMC-07	Metal detector	question 1	green
TMC-16	Metal detector	question 1	green
TMC-17	Metal detector	question 1	green
TDD-17	De-duster	question 1	green
TDD-10	De-duster	question 1	green
TDD-12	De-duster	question 1	green
VAC-16	Portable vaccum cleaner	question 1	green
ENC-06	Encapsulation	question 1	green
TMC-09	Metal detector	question 1	green
CLM-01	Capsule loader	question 1	green
CSM-01	Capsule sorter	question 1	green
CSM-02	Capsule sorter	question 1	green
CEL-01	Capsule elevator	question 1	green
CPS-02	Capsule polisher	question 1	green
CPS-03	Capsule polisher	question 1	green
SCL-19	Balance	question 1	green
SCL-90	Balance	question 1	green
MIS-20	Overhead Stirrer	question 1	green
MIS-18	Overhead Stirrer	question 1	green
TCT-01	Accela cota	question 1	red
INV-01	LD 60 Bowl Inverter	question 1	green
DIS-02	Dispensary	question 1	green
PMP-02	Pump	question 1	green
PMP-25	Pump	question 1	green
FRI-07	Friability Tester	question 1	green
FRI-04	Friability Tester	question 1	green
HRD-12	Hardness tester	question 1	green
TDD-11	De-duster	question 1	green
DIS-09	Disintegration bath	question 1	green
DIS-05	Disintegration bath	question 1	green
HRD-13	Hardness tester	question 1	green
HRD-11	Hardness tester	question 1	green
HRD-14	Hardness tester	question 1	green
SCL-67	Balance	question 1	green
SCL-105	Balance	question 1	green
DIS-06	Disintegration bath	question 1	green
MIS-25	Overhead Stirrer	question 1	green
SCL-17	Balance	question 2	green
SCL-83	Balance	question 2	green
SCL-100	Balance	question 2	green

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

Many thanks

 

[mse330]

Hey ralsnz,

I have more questions after seeing your bigger sample

• I have noticed that "Equipment number" is NOT a unique identifier. For example, Equipment number "SCL-90" is repeated both in room 5 & room 8 - Is this an error in the sample data or it could actually happen ? If it does happen, should I repeated it twice in both rooms 5 & 8 ?

• Equipment # "MIS-18" repeated twice in the 2nd table - In the sample it has the same question answers but what if it has different answers? Which one should I consider ?