Best method of extracting data from large dataset

[Manchesterpolstudent]

Hi

Apologies if something similar has been posted before, but I was hoping for some help with a problem I've been having with a project I'm working on.

I'm working with a large dataset (approx 200,000 rows), looking at a large number of petitions (approx 10,000). which a sample of people (approx 1,000) have signed.

The problem I have is that I need to produce a list of which people have signed the same petitions as each other. The data is in the below format (each row a signature, with columns including the name of respondent, name of petition etc).


Respondent name Petition name var2 var3
Person 1 1
Person 3 1
Person 6 1
Person 1 2
Person 2 2
Person 1 3
Person 17 3

Eventually, I want to be able to have a matrix showing the respondents as columns & rows, with the cells showing the no. of petitions signed by both respondents. However, I'm struggling to know how to get this information without taking months and months!

Any help would be greatly appreciated, please let me know if you need anything else which I've missed out.

Many thanks

Andrew

[FDibbins]

Hi, welcome to the forum

To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[Manchesterpolstudent]

Hi

Thanks for getting back to me, I've attached a file with some dummy data.

I've included the data for three of the petitions (so 44 rows showing individual signatures). I've removed some of the data that I'm not concerned about, but have left in name of signatory (anonymised), their unique ID, and the ID of the petition.

Hope that's clear, but please let me know if you need anything else.

Many thanks

Andrew