Interesting Multiple Coincidences or Pivot Table Problem

J

Josue Barocio

Board Regular
Joined
Mar 8, 2002
Messages
101
I need a unique type of Pivot (I think) table. The file below explains what is needed better than I can in words.

The input file has the name of assemblies in column A and a list of the components needed to produce the assembly in column B. In the list, part 10 is used in assemblies Control and Sensor. Part 14 is used in the Control and Dynamic assemblies and part 20 is used in the Sensor and Dynamic assemblies.

The output file needs to be a matrix identifying at a glance assemblies that have common parts, Control, Sensor, and Dynamic in the example. The only assembly with unique parts is the Titan.

I can approach the objective with a Pivot table, but I have not been able to avoid listing all the components (17 in the example, about 50 assemblies and 1200 records in the actual live file)

Any ideas about functions that may help get to the desired output file would be appreciated. I've tried transposing, vlookup, hlookup, match, etc.

Thanks,

Joe
Matrix.xls
ABCDE
1INPUTFILE
2ASSEMBLYPART
3Control10
4Control11
5Control12
6Control13
7Control14
8Sensor20
9Sensor21
10Sensor10
11Sensor22
12Dynamic30
13Dynamic14
14Dynamic20
15Dynamic31
16Dynamic32
17Titan40
18Titan41
19Titan42
20
21OUTPUTFILE
22ControlSensorDynamicTitan
23Controlxxx
24Sensorxxx
25Dynamicxxx
26Titanx
Sheet1
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Sort by date Sort by votes
Ok, here's what I did.

First, I named the range A2:B19 (The INPUT FILE) as List. Saved the workbook after that as "Test.xls"

Then, went to Data | Import External Data, New Database query.

In the Databases lists, I chose "Excel files"

In the book choices, I chose "Test.xls"

In the First step of the Query Wizard, I added "List" to my query.

Clicked "Next" three times, to go to the last step of the Query Wizard. In there, I chose "View data or edit query in Microsoft Query"

Once in there, I click on the "Add table" button.

Selected "List" again from the choices, MS Query will warn that it is already added, and ask if you want to add it again. Answer "Yes".

So now you have two tables, "List", and "List_1". Drag "PART" from either one, and drop it into the other one, to link the two tables.

Then, if you have the PART column in the query, delete it, and add the ASSEMBLY from List_1 to the query.

If you refresh the Query you'll see all the possible matches that have the same part, with some of them repeated (I get 3 Titan - Titan combinations), so now, we have to make a small edit to the SQL.

Mine looks like this:

Code: 
SELECT List.ASSEMBLY, List_1.ASSEMBLY
FROM `C:\WINDOWS\Desktop\Test`.List List, `C:\WINDOWS\Desltop\Test`.List List_1
WHERE List.PART = List_1.PART

just add a DISTINCT after the SELECT, so it looks like

Code: 
SELECT DISTINCT List.ASSEMBLY, List_1.ASSEMBLY
FROM `C:\WINDOWS\Desktop\Test`.List List, `C:\WINDOWS\Desltop\Test`.List List_1
WHERE List.PART = List_1.PART

Ok now, click the "Return Data" button to get the info back to Excel.

In the Import Data wizard, I chose to create a new Pivot Table report, and layed it out like this:
Test.xls
ABCDEF
3CountofASSEMBLYASSEMBLY2
4ASSEMBLYControlDynamicSensorTitanGrandTotal
5Control1113
6Dynamic1113
7Sensor1113
8Titan11
9GrandTotal333110
Sheet2


You could format the field as

x

to get the exact layout that you showed in your post.

Wow this was long...
 
Upvote 0
That's fantastic Juan Pablo! I'll try your solution in the morning with a fresher mind and comment back to you. You have opened a whole new world of Excel I never knew existed (Database Query).

Muchisimas gracias!

Joe
 
Upvote 0
Juan Pable,
Your solution worked like clockwork. The only snag was that I had never installed Database Query in my PC so I had to load it from the MS Office CD. Other than that and a couple of stumbles using Query nthe first time, your instructions worked flawlessly.

Mil gracias otra vez.

Joe
 
Upvote 0
Good job, Juan. I thought it should be possible to tackle this challenging question with a formula, morefunc aiding...
josue.xls
ABCDEFGH
1INPUTFILEOUTPUTFILE
2ASSEMBLYPARTControlSensorDynamicTitan
3Control10Controlxxx 
4Control11Sensorxxx 
5Control12Dynamicxxx 
6Control13Titan   x
7Control14
8Sensor20
9Sensor21
10Sensor10
11Sensor22
12Dynamic30
13Dynamic14
14Dynamic20
15Dynamic31
16Dynamic32
17Titan40
18Titan41
19Titan42
Sheet1


The formula in E3, which is copied across then down, is...

=IF($D3=E$2,"x",IF(COUNT(MATCH(EVAL("{"&SUBSTITUTE(MCONCAT(IF($A$3:$A$19=$D3,","&$B$3:$B$19,"")),",","",1)&"}"),($A$3:$A$19=E$2)*$B$3:$B$19,0)),"x",""))

which must be array-entered.

Caveat. The solution might run up against the limitation of MCONCAT (255 chars).
 
Upvote 0
D8 Control Sensor Dynamic Titan
Control
Sensor
Dynamic
Titan



With $B$3:$B$7 named Control etc

Array enter

=IF(SUM(COUNTIF(INDIRECT(E$8),INDIRECT($D9)))>0,"x","")

=IF(SUM(COUNTIF(Titan,B3:B19))>COUNT(Titan),"x","")

.
 
Upvote 0
Aladin Akyurek said:
Good job, Juan. I thought it should be possible to tackle this challenging question with a formula, morefunc aiding...

The formula in E3, which is copied across then down, is...

=IF($D3=E$2,"x",IF(COUNT(MATCH(EVAL("{"&SUBSTITUTE(MCONCAT(IF($A$3:$A$19=$D3,","&$B$3:$B$19,"")),",","",1)&"}"),($A$3:$A$19=E$2)*$B$3:$B$19,0)),"x",""))

which must be array-entered.

Caveat. The solution might run up against the limitation of MCONCAT (255 chars).
Click to expand...
Cool formula Aladin, altough I think that you don't need MoreFunc for this one, since the IF() can already return the array that you need for MATCH ?

Like this:
Book1
ABCDEFGH
1INPUTFILE
2ASSEMBLYPARTControlSensorDynamicTitan
3Control10Controlxxx 
4Control11Sensorxxx 
5Control12Dynamicxxx 
6Control13Titan   x
7Control14
8Sensor20
9Sensor21
10Sensor10
11Sensor22
12Dynamic30
13Dynamic14
14Dynamic20
15Dynamic31
16Dynamic32
17Titan40
18Titan41
19Titan42
Sheet1


Array formula in E3 is

=IF($D3=E$2,"x",IF(COUNT(MATCH(IF($A$3:$A$19=$D3,$B$3:$B$19,""),($A$3:$A$19=E$2)*$B$3:$B$19,0)),"x",""))

also copied across and down.
 
Upvote 0
Juan Pablo González said:
Aladin Akyurek said:
Good job, Juan. I thought it should be possible to tackle this challenging question with a formula, morefunc aiding...

The formula in E3, which is copied across then down, is...

=IF($D3=E$2,"x",IF(COUNT(MATCH(EVAL("{"&SUBSTITUTE(MCONCAT(IF($A$3:$A$19=$D3,","&$B$3:$B$19,"")),",","",1)&"}"),($A$3:$A$19=E$2)*$B$3:$B$19,0)),"x",""))

which must be array-entered.

Caveat. The solution might run up against the limitation of MCONCAT (255 chars).
Click to expand...
Cool formula Aladin, altough I think that you don't need MoreFunc for this one, since the IF() can already return the array that you need for MATCH ?
...

Array formula in E3 is

=IF($D3=E$2,"x",IF(COUNT(MATCH(IF($A$3:$A$19=$D3,$B$3:$B$19,""),($A$3:$A$19=E$2)*$B$3:$B$19,0)),"x",""))

also copied across and down.
Click to expand...

Indeed, since an array consisting of non-zero numbers and formula-blanks are matched against an array of numbers including zero's but no formula-blanks. In my initial attemps I had formula-blanks in both arrays where I used morefunc to eliminate them from the first. That's why I must have overlooked the formula-blanks vs zeroes bit. :LOL: As you must have noticed, COUNT is the essential ingredient.
 
Upvote 0
JPG
Is it necessary to follow the route of MS Query?
I just copied the Column of Assembly into another Column & then Made a pivot in which both row & Column field has Assembly as field & the Data field has part into it; This also solves the problem.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
Kitting Spreadsheet
2 3
Replies
24
Views
679
DocRogers
D
X
  • Question
Formula bar displaying SUM formula, but not its arguments
Replies
4
Views
312
KRice
K
A
  • Question
Dynamically add new rows via VBA when using IFERROR TEXTJOIN and FILTER formulas from Table?
Replies
0
Views
156
AlfredDB
A
M
  • Question
remote pivot table
Replies
0
Views
137
MetLife
M
M
  • Question
Excel pivot table query - retaining filters on pivot in absence of data
2
Replies
10
Views
1K
Alex Blakenburg
A

Forum statistics

Threads
1,215,640
Messages
6,125,972
Members
449,276
Latest member
surendra75

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