Building a pivot with a massive data set

[ascrit]

Hello all

First time poster and I am hoping for some kind help today.

I have a large set of data that I would like to organize quickly. Currently, the data is set in two columns. Column A contains parent names (almost 5800) and column B contains child information. The child information can contain multiple values (in some cases hundreds of values) which are separated by a comma.

My goal is to be able to select a particular child value and return all parents which have that child.

I assume that this can be accomplished with a pivot table yet I am unclear on how to arrange the data in order to build an effective pivot.

Can anyone help?

 

[Gerald Higgins]

Hi, welcome to the board.

Can you give us some examples of what your data looks like ?
If you don't want to post actual data, make up some dummy data that incorporates the basic characteristics, and post that.
For example, for the "child" information, it sounds as if you maybe have several entries in a single cell.
What exactly does that look like ?

 

[ascrit]

Hi Gerald

Here is some example data, I hope it is not too difficult to understand:

[TABLE="width: 217"]
<tbody>[TR]
[TD]PARENT_NAME[/TD]
[TD]CHILD_NAME[/TD]
[/TR]
[TR]
[TD]analytica[/TD]
[TD]electronic records storage[/TD]
[/TR]
[TR]
[TD]appwpartprd01[/TD]
[TD]bmc performance manager 4.3.00 on appwpartprd01, bmc performance manager portal rtserver (bmc oem smartsockets) 6.8 on appwpartprd01, tmart - production, tm art, bmc tm art execution server 4.1 on appwpartprd01, patrol - production[/TD]
[/TR]
[TR]
[TD]appwsp2010tst04[/TD]
[TD]vistive - test, sharepoint services on appwsp2010tst04, vistive gold - test, stewart seeds - test, jung seed genetics - test, deltapine - test, sharepoint dmz farm b - test, genuitytraits ca - test, microsoft iis webserver 7.0 on appwsp2010tst04, acceleron sts- test[/TD]
[/TR]
[TR]
[TD]stlubpmdev01[/TD]
[TD]field compliance manager (fcm) - test, ibm was 7.0 identified as twperfsvr2, node: lombardinode02, cell: lombardicell01 on stlubpmprd02, ibm was 7.0 identified as twprocsvr2, node: lombardinode02, cell: lombardicell01 on stlubpmtst02, ibm was 7.0 identified as twprocsvr6, node: lombardinode06, cell: lombardicell01 on stlubpmprd06, inventory manager - test, ibm was 7.0 identified as twprocsvr3, node: lombardinode03, cell: lombardicell01 on stlubpmprd03, ibm was 7.0 identified as twperfsvr5, node: lombardinode05, cell: lombardicell01 on stlubpmtst05, sequencing tracking analysis & reporting (star) - development, sequencing tracking analysis & reporting (star) - test, greenhouse research operations (gro) - test, ibm was 7.0 identified as twprocsvr2, node: lombardinode02, cell: lombardicell01 on stlubpmprd02, sequencing tracking analysis & reporting (star) - production support, wle twperfsvr - development, ibm was 7.0 identified as twprocsvr4, node: lombardinode04, cell: lombardicell01 on stlubpmtst04, greenhouse research operations (gro) - development, inventory manager - development, ibm was 7.0 identified as twperfsvr6, node: lombardinode06, cell: lombardicell01 on stlubpmprd06, ibm was 7.0 identified as twprocsvr4, node: lombardinode04, cell: lombardicell01 on stlubpmpdwdev01, ibm was 7.0 identified as twprocsvr, node: lombardinode01, cell: lombardicell01 on stlubpmpst01, ibm was 7.0 identified as twprocsvr4, node: lombardinode04, cell: lombardicell01 on stlubpmpst04, ibm was 7.0 identified as twperfsvr6, node: lombardinode06, cell: lombardicell01 on stlubpmtst06, ibm was 7.0 identified as twprocsvr, node: lombardinode01, cell: lombardicell01 on stlubpmdev01, ibm was 7.0 identified as twperfsvr4, node: lombardinode04, cell: lombardicell01 on stlubpmtst04, ibm was 7.0 identified as twprocsvr5, node: lombardinode05, cell: lombardicell01 on stlubpmpst05, ibm was 7.0 identified as twperfsvr2, node: lombardinode02, cell: lombardicell01 on stlubpmtst02, ibm was 7.0 identified as twprocsvr6, node: lombardinode06, cell: lombardicell01 on stlubpmpst06, ibm was 7.0 identified as twprocsvr, node: lombardinode01, cell: lombardicell01 on stlubpmtst01, ibm was 7.0 identified as twprocsvr6, node: lombardinode06, cell: lombardicell01 on stlubpmtst06, ibm was 7.0 identified as twperfsvr5, node: lombardinode05, cell: lombardicell01 on stlubpmprd05, field compliance manager (fcm) - production support, field compliance manager (fcm) - development, apache derby database engine 10.5 on stlubpmdev01, ibm was 7.0 identified as twprocsvr5, node: lombardinode05, cell: lombardicell01 on stlubpmprd05, ibm was 7.0 identified as twprocsvr, node: lombardinode01, cell: lombardicell01 on stlubpmprd01, ibm was 7.0 identified as twprocsvr2, node: lombardinode02, cell: lombardicell01 on stlubpmdev02, ibm was 7.0 identified as twprocsvr2, node: lombardinode02, cell: lombardicell01 on stlubpmpst02, cloning manager - test, cloning manager - development, ibm was 7.0 identified as twperfsvr3, node: lombardinode03, cell: lombardicell01 on stlubpmprd03, greenhouse research operations (gro) - production support, wle twperfsvr - production, ibm was 7.0 identified as twperfsvr, node: lombardinode01, cell: lombardicell01 on stlubpmdev01, ibm was 7.0 identified as twprocsvr4, node: lombardinode04, cell: lombardicell01 on stlubpmprd04, ibm was 7.0 identified as twprocsvr5, node: lombardinode05, cell: lombardicell01 on stlubpmtst05, ibm was 7.0 identified as twprocsvr3, node: lombardinode03, cell: lombardicell01 on stlubpmdev03, ibm was 7.0 identified as twperfsvr3, node: lombardinode03, cell: lombardicell01 on stlubpmtst03, ibm was 7.0 identified as twperfsvr, node: lombardinode01, cell: lombardicell01 on stlubpmprd01, wle twperfsvr, ibm was 7.0 identified as twperfsvr4, node: lombardinode04, cell: lombardicell01 on stlubpmprd04, ibm was 7.0 identified as twprocsvr3, node: lombardinode03, cell: lombardicell01 on stlubpmpst03, ibm was 7.0 identified as twperfsvr, node: lombardinode01, cell: lombardicell01 on stlubpmtst01, ibm was 7.0 identified as twprocsvr3, node: lombardinode03, cell: lombardicell01 on stlubpmtst03, ibm was 7.0 identified as twperfsvr2, node: lombardinode02, cell: lombardicell01 on stlubpmdev02, apache derby database engine on stlubpmdev01, ibm was 7.0 identified as twperfsvr3, node: lombardinode03, cell: lombardicell01 on stlubpmdev03[/TD]
[/TR]
[TR]
[TD]stluccmprd01[/TD]
[TD]webex - production[/TD]
[/TR]
[TR]
[TD]wolffish[/TD]
[TD]file services on wolffish, microsoft iis webserver 7.0 on wolffish, file services - site 1000[/TD]
[/TR]
[TR]
[TD]woprn01[/TD]
[TD]print services on woprn01, print services - site 2401[/TD]
[/TR]
[TR]
[TD]wpg_blue[/TD]
[TD]management reporting system -dev, change tracking system -dev, dwhd on wpg_blue, forecasting systems -dev, customer management system -dev, sales rep portal -dev, dev on wpg_blue, reference data -dev, intellectual property protection -dev, file loader -dev, retail tracking system -dev, srs -dev, program information management system (pims) - development[/TD]
[/TR]
[TR]
[TD]zphastldmzprd01b[/TD]
[TD]tippingpoint on zphastldmzprd01b[/TD]
[/TR]
[TR]
[TD]zydeco[/TD]
[TD]file services - site 1560, file services on zydeco[/TD]
[/TR]
</tbody>[/TABLE]

 

[Gerald Higgins]

So are you saying, for example for "parent" zydeco, this is associated with two "children", "file services - site 1560" and "file services on zydeco", and you want to analyse each of them separately ?

 

[ascrit]

Luckily, I have been able to find a solution to this problem. I appreciate your response to this query Gerald!