Data manipulation question - simple at first sight, but has me stumped!

[stefanaalten]

I'm still looking for some help with what at first sight looks like some simple data manipulation but it has me stumped! I have data on books and authors, like this:

Author1 Book1
Author1 Book2
Author1 Book3
Author2 Book1
Author2 Book3
Author2 Book4
Author3 Book5
Author3 Book6
Author4 Book7
Author5 Book7

Authors may have produced more than one book and each book may have multiple authors.

I need to turn those cases where any two books are "related" (having same author) into rows like this:

Book1 Book2 Author1
Book1 Book3 Author1
Book2 Book3 Author1
Book1 Book3 Author2
Book1 Book4 Author2
Book3 Book4 Author2
Book5 Book6 Author3

Note that in the example Author4 and Author5 and Book7 do not feature in the second list: Author4 and Author5 have not worked on any other book(s), and Book7 is not related to another book produced by the same author(s).

I don't have masses of data yet, but as the list grows it will become impractical to do this completely manually. I would really to know how this can be automated, whether via a general workflow (e.g. involving copying-pasting), or a fully-automated formula-driven solution.

I will then use this data to construct a graph using Gephi, with the nodes representing books, and the edges (connecting the nodes) representing the book-pairs.

Can anyone help please? Many thanks in advance!

P.S. I had posted a similar question on this previously but it was not clearly stated, so hope it is alright to re-ask (?)

 

[MickG]

With your data in columns "A & B", Try this for results starting "D1".

[COLOR="Navy"]Sub[/COLOR] MG15Feb07
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Q [COLOR="Navy"]As[/COLOR] Variant, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] pp [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] K [COLOR="Navy"]As[/COLOR] Variant, P [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] nRay() [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Not .Exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
        ReDim Ray(1 To 1)
        Ray(1) = Dn.Offset(, 1).Value
        .Add Dn.Value, Ray
    [COLOR="Navy"]Else[/COLOR]
         Q = .Item(Dn.Value)
            ReDim Preserve Q(1 To UBound(Q) + 1)
            Q(UBound(Q)) = Dn.Offset(, 1).Value
        .Item(Dn.Value) = Q
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] K [COLOR="Navy"]In[/COLOR] .keys
[COLOR="Navy"]For[/COLOR] P = 1 To UBound(.Item(K))
    [COLOR="Navy"]For[/COLOR] pp = P To UBound(.Item(K))
        [COLOR="Navy"]If[/COLOR] Not .Item(K)(P) = .Item(K)(pp) [COLOR="Navy"]Then[/COLOR]
            ReDim Preserve nRay(1 To 3, 0 To c)
                nRay(1, c) = .Item(K)(P)
                nRay(2, c) = .Item(K)(pp)
                nRay(3, c) = K
                c = c + 1
            [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]Next[/COLOR] pp
    [COLOR="Navy"]Next[/COLOR] P
[COLOR="Navy"]Next[/COLOR] K
[COLOR="Navy"]End[/COLOR] With
Range("D1").Resize(c, 3) = Application.Transpose(nRay)
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[oldbrewer]

name

book

book occurrences in list

author occurrences in list

Author1

Book1

2

3

Author1

Book2

1

3

Author1

Book3

2

3

Count of book

book

Author2

Book1

2

3

name

Book1

Book2

Book3

Book4

Book5

Book6

Book7

Grand Total

Author2

Book3

2

3

Author1

1

1

1

3

Author2

Book4

1

3

Author2

1

1

1

3

Author3

Book5

1

2

Author3

1

1

2

Author3

Book6

1

2

Author4

1

1

Author4

Book7

2

1

Author5

1

1

Author5

Book7

2

1

Grand Total

2

1

2

1

1

1

2

10

Authors may have produced more than one book and each book may have multiple authors.

I need to turn those cases where any two books are "related" (having same author) into rows like this:

a simple pivot table as above tells us eg that authors 1 and 2 did book 1

Book1 Book2 Author1

it also shows that author1 did books 1,2, and 3

Book1 Book3 Author1

Book2 Book3 Author1

giving rise to the combinations 1-2 1-3 2-3

Book1 Book3 Author2

Book1 Book4 Author2

Book3 Book4 Author2

Book5 Book6 Author3

Author1

Book1

Book2

Book3

Author2

Book1

Book3

Book4

Author3

Book5

Book6

Author4

Book7

Author5

Book7

the lower table is just replacing "1" with the book title

do these tables help ?

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