Hello Everyone,
From what I thought would be a straight forward function, this has had me scratching my head far too often this week. Until now, I've been manually sorting and copy/pasting in data, but this enhances the possibilities for errors. I've been trying to use a vlookup and index/match solutions, but with the size of the dataset, it becomes unstable. So I'm looking for a VBA solution. Or if Excel already has a feature like this inplace, that would be good too.
I Apologise for the length of the post, I tried to make it as informative as possible
The issue I'm having is that I want to merge several sheets of data into one master file, but each file may have redundant data, duplicated data, incorrect data and so on. Below is an example of the issue
I have created a Master_file and include information of the subjects. ‘Previous IDs’ have been converted into their current/‘running Id’s’, in some cases more than one ‘running ID’ has been made from a ‘Previous ID’. A ‘Previous ID’ will only have one genotype for each genotype group, however due to mistakes in generating data, ‘running Ids’ from the same ’Previous ID’ may show different results (One thing I want to detect).
I then want to merge all of the results into the one Master file, while highlighting the erroneous data:
Sheet1
<colgroup><col style="font-weight:bold; width:30px; "><col style="width:146px;"><col style="width:84px;"><col style="width:77px;"><col style="width:75px;"><col style="width:75px;"><col style="width:75px;"><col style="width:75px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="align: center"]Master_file:[/TD]
[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: center"]Running_ID[/TD]
[TD="align: center"]Previous_ID[/TD]
[TD="align: center"]Gender[/TD]
[TD="align: center"]Genotype1[/TD]
[TD="align: center"]Genotype2[/TD]
[TD="align: center"]Genotype3[/TD]
[TD="align: center"]Genotype4[/TD]
[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: center"]51[/TD]
[TD="align: center"]100[/TD]
[TD="align: center"]M[/TD]
[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: center"]52[/TD]
[TD="align: center"]100[/TD]
[TD="align: center"]M[/TD]
[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: center"]53[/TD]
[TD="align: center"]101[/TD]
[TD="align: center"]F[/TD]
[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: center"]54[/TD]
[TD="align: center"]102[/TD]
[TD="align: center"]M[/TD]
[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: center"]55[/TD]
[TD="align: center"]103[/TD]
[TD="align: center"]F[/TD]
[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="align: center"]56[/TD]
[TD="align: center"]103[/TD]
[TD="align: center"]F[/TD]
[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD="align: center"]57[/TD]
[TD="align: center"]103[/TD]
[TD="align: center"]F[/TD]
</tbody>
Excel tables to the web >> Excel Jeanie HTML 4
Sheet1
<colgroup><col style="font-weight:bold; width:30px; "><col style="width:146px;"><col style="width:84px;"><col style="width:77px;"><col style="width:75px;"><col style="width:75px;"><col style="width:75px;"><col style="width:75px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]11[/TD]
[TD="align: center"]Datasheet1:[/TD]
[TD="bgcolor: #cacaca, align: center"]12[/TD]
[TD="align: center"]Running_ID [/TD]
[TD="align: center"]Genotype1[/TD]
[TD="align: center"]Genotype2[/TD]
[TD="align: center"]Genotype3[/TD]
[TD="align: center"]Genotype4[/TD]
[TD="bgcolor: #cacaca, align: center"]13[/TD]
[TD="align: center"]51[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #cacaca, align: center"]14[/TD]
[TD="align: center"]53[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="bgcolor: #cacaca, align: center"]15[/TD]
[TD="align: center"]57[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
</tbody>
Excel tables to the web >> Excel Jeanie HTML 4
Sheet1
<colgroup><col style="font-weight:bold; width:30px; "><col style="width:146px;"><col style="width:84px;"><col style="width:77px;"><col style="width:75px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]17[/TD]
[TD="align: center"]Datasheet2:[/TD]
[TD="bgcolor: #cacaca, align: center"]18[/TD]
[TD="align: center"]Running_ID [/TD]
[TD="align: center"]Genotype2[/TD]
[TD="align: center"]Genotype3[/TD]
[TD="align: center"]Genotype4[/TD]
[TD="bgcolor: #cacaca, align: center"]19[/TD]
[TD="align: center"]52[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #cacaca, align: center"]20[/TD]
[TD="align: center"]53[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="bgcolor: #cacaca, align: center"]21[/TD]
[TD="align: center"]55[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
</tbody>
Excel tables to the web >> Excel Jeanie HTML 4
Sheet1
<colgroup><col style="font-weight:bold; width:30px; "><col style="width:146px;"><col style="width:84px;"><col style="width:77px;"><col style="width:75px;"><col style="width:75px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]24[/TD]
[TD="align: center"]Datasheet3:[/TD]
[TD="bgcolor: #cacaca, align: center"]25[/TD]
[TD="align: center"]Running_ID [/TD]
[TD="align: center"]Previous_ID [/TD]
[TD="align: center"]Genotype2[/TD]
[TD="align: center"]Genotype3[/TD]
[TD="align: center"]Genotype4[/TD]
[TD="bgcolor: #cacaca, align: center"]26[/TD]
[TD="align: center"]100[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #cacaca, align: center"]27[/TD]
[TD="align: center"]102[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="bgcolor: #cacaca, align: center"]28[/TD]
[TD="align: center"]103[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
</tbody>
Excel tables to the web >> Excel Jeanie HTML 4
The order I feel it would have to happen in is that Datasheet3 will import first and lay down the background (because the Previous Id’s will cover several running_IDs).
Then each datasheet set will be imported to its respective running ID (They will not always be in the same order), and the data for the genotype (Will not always be in the same order, but will always have the same name) will be compared follow these rules:
If the importing genotype is the same = don’t import,
if the importing genotype is not existing yet = import,
if the importing genotype shows “-“ or “N/A” or etc=import,
if the importing genotypes are different = flag/Mark/Highlight etc)
To give a result like the table below:
Sheet1
<colgroup><col style="font-weight:bold; width:30px; "><col style="width:146px;"><col style="width:84px;"><col style="width:77px;"><col style="width:75px;"><col style="width:75px;"><col style="width:75px;"><col style="width:75px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]32[/TD]
[TD="align: center"]Updated_Master_File:[/TD]
[TD="bgcolor: #cacaca, align: center"]33[/TD]
[TD="align: center"]Running_ID[/TD]
[TD="align: center"]Previous_ID[/TD]
[TD="align: center"]Gender [/TD]
[TD="align: center"]Genotype1[/TD]
[TD="align: center"]Genotype2[/TD]
[TD="align: center"]Genotype3[/TD]
[TD="align: center"]Genotype4[/TD]
[TD="bgcolor: #cacaca, align: center"]34[/TD]
[TD="align: center"]51[/TD]
[TD="align: center"]100[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1/0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #cacaca, align: center"]35[/TD]
[TD="align: center"]52[/TD]
[TD="align: center"]100[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #cacaca, align: center"]36[/TD]
[TD="align: center"]53[/TD]
[TD="align: center"]101[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="bgcolor: #cacaca, align: center"]37[/TD]
[TD="align: center"]54[/TD]
[TD="align: center"]102[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="bgcolor: #cacaca, align: center"]38[/TD]
[TD="align: center"]55[/TD]
[TD="align: center"]103[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="bgcolor: #cacaca, align: center"]39[/TD]
[TD="align: center"]56[/TD]
[TD="align: center"]103[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="bgcolor: #cacaca, align: center"]40[/TD]
[TD="align: center"]57[/TD]
[TD="align: center"]103[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
</tbody>
Excel tables to the web >> Excel Jeanie HTML 4
The Database I have is expanding, with additional datasheets being added to it. At the moment I have about 4400 rows by 280 columns. I’m happy to re-run the analysis everytime for an 'Updated_Master_file'.
I would really appreciate if someone could help me with this, or could point me in the right direction.
Thank you
Steve.
From what I thought would be a straight forward function, this has had me scratching my head far too often this week. Until now, I've been manually sorting and copy/pasting in data, but this enhances the possibilities for errors. I've been trying to use a vlookup and index/match solutions, but with the size of the dataset, it becomes unstable. So I'm looking for a VBA solution. Or if Excel already has a feature like this inplace, that would be good too.
I Apologise for the length of the post, I tried to make it as informative as possible
The issue I'm having is that I want to merge several sheets of data into one master file, but each file may have redundant data, duplicated data, incorrect data and so on. Below is an example of the issue
I have created a Master_file and include information of the subjects. ‘Previous IDs’ have been converted into their current/‘running Id’s’, in some cases more than one ‘running ID’ has been made from a ‘Previous ID’. A ‘Previous ID’ will only have one genotype for each genotype group, however due to mistakes in generating data, ‘running Ids’ from the same ’Previous ID’ may show different results (One thing I want to detect).
I then want to merge all of the results into the one Master file, while highlighting the erroneous data:
Sheet1
| * | A | B | C | D | E | F | G |
| * | * | * | * | * | * | ||
| * | * | * | * | ||||
| * | * | * | * | ||||
| * | * | * | * | ||||
| * | * | * | * | ||||
| * | * | * | * | ||||
| * | * | * | * | ||||
| * | * | * | * |
<colgroup><col style="font-weight:bold; width:30px; "><col style="width:146px;"><col style="width:84px;"><col style="width:77px;"><col style="width:75px;"><col style="width:75px;"><col style="width:75px;"><col style="width:75px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="align: center"]Master_file:[/TD]
[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: center"]Running_ID[/TD]
[TD="align: center"]Previous_ID[/TD]
[TD="align: center"]Gender[/TD]
[TD="align: center"]Genotype1[/TD]
[TD="align: center"]Genotype2[/TD]
[TD="align: center"]Genotype3[/TD]
[TD="align: center"]Genotype4[/TD]
[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: center"]51[/TD]
[TD="align: center"]100[/TD]
[TD="align: center"]M[/TD]
[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: center"]52[/TD]
[TD="align: center"]100[/TD]
[TD="align: center"]M[/TD]
[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: center"]53[/TD]
[TD="align: center"]101[/TD]
[TD="align: center"]F[/TD]
[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: center"]54[/TD]
[TD="align: center"]102[/TD]
[TD="align: center"]M[/TD]
[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: center"]55[/TD]
[TD="align: center"]103[/TD]
[TD="align: center"]F[/TD]
[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="align: center"]56[/TD]
[TD="align: center"]103[/TD]
[TD="align: center"]F[/TD]
[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD="align: center"]57[/TD]
[TD="align: center"]103[/TD]
[TD="align: center"]F[/TD]
</tbody>
Excel tables to the web >> Excel Jeanie HTML 4
Sheet1
| * | A | B | C | D | E | F | G |
| * | * | * | * | * | * | ||
| * | * | ||||||
| * | * | * | * | ||||
| * | * | * | * | ||||
| * | * | * | * |
<colgroup><col style="font-weight:bold; width:30px; "><col style="width:146px;"><col style="width:84px;"><col style="width:77px;"><col style="width:75px;"><col style="width:75px;"><col style="width:75px;"><col style="width:75px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]11[/TD]
[TD="align: center"]Datasheet1:[/TD]
[TD="bgcolor: #cacaca, align: center"]12[/TD]
[TD="align: center"]Running_ID [/TD]
[TD="align: center"]Genotype1[/TD]
[TD="align: center"]Genotype2[/TD]
[TD="align: center"]Genotype3[/TD]
[TD="align: center"]Genotype4[/TD]
[TD="bgcolor: #cacaca, align: center"]13[/TD]
[TD="align: center"]51[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #cacaca, align: center"]14[/TD]
[TD="align: center"]53[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="bgcolor: #cacaca, align: center"]15[/TD]
[TD="align: center"]57[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
</tbody>
Excel tables to the web >> Excel Jeanie HTML 4
Sheet1
| * | A | B | C | D |
| * | * | * | ||
<colgroup><col style="font-weight:bold; width:30px; "><col style="width:146px;"><col style="width:84px;"><col style="width:77px;"><col style="width:75px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]17[/TD]
[TD="align: center"]Datasheet2:[/TD]
[TD="bgcolor: #cacaca, align: center"]18[/TD]
[TD="align: center"]Running_ID [/TD]
[TD="align: center"]Genotype2[/TD]
[TD="align: center"]Genotype3[/TD]
[TD="align: center"]Genotype4[/TD]
[TD="bgcolor: #cacaca, align: center"]19[/TD]
[TD="align: center"]52[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #cacaca, align: center"]20[/TD]
[TD="align: center"]53[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="bgcolor: #cacaca, align: center"]21[/TD]
[TD="align: center"]55[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
</tbody>
Excel tables to the web >> Excel Jeanie HTML 4
Sheet1
| * | A | B | C | D | E |
| * | * | * | * | ||
| * | |||||
| * | |||||
| * |
<colgroup><col style="font-weight:bold; width:30px; "><col style="width:146px;"><col style="width:84px;"><col style="width:77px;"><col style="width:75px;"><col style="width:75px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]24[/TD]
[TD="align: center"]Datasheet3:[/TD]
[TD="bgcolor: #cacaca, align: center"]25[/TD]
[TD="align: center"]Running_ID [/TD]
[TD="align: center"]Previous_ID [/TD]
[TD="align: center"]Genotype2[/TD]
[TD="align: center"]Genotype3[/TD]
[TD="align: center"]Genotype4[/TD]
[TD="bgcolor: #cacaca, align: center"]26[/TD]
[TD="align: center"]100[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #cacaca, align: center"]27[/TD]
[TD="align: center"]102[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="bgcolor: #cacaca, align: center"]28[/TD]
[TD="align: center"]103[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
</tbody>
Excel tables to the web >> Excel Jeanie HTML 4
The order I feel it would have to happen in is that Datasheet3 will import first and lay down the background (because the Previous Id’s will cover several running_IDs).
Then each datasheet set will be imported to its respective running ID (They will not always be in the same order), and the data for the genotype (Will not always be in the same order, but will always have the same name) will be compared follow these rules:
If the importing genotype is the same = don’t import,
if the importing genotype is not existing yet = import,
if the importing genotype shows “-“ or “N/A” or etc=import,
if the importing genotypes are different = flag/Mark/Highlight etc)
To give a result like the table below:
Sheet1
| * | A | B | C | D | E | F | G |
| * | * | * | * | * | * | ||
| * | |||||||
| * | |||||||
| * |
<colgroup><col style="font-weight:bold; width:30px; "><col style="width:146px;"><col style="width:84px;"><col style="width:77px;"><col style="width:75px;"><col style="width:75px;"><col style="width:75px;"><col style="width:75px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]32[/TD]
[TD="align: center"]Updated_Master_File:[/TD]
[TD="bgcolor: #cacaca, align: center"]33[/TD]
[TD="align: center"]Running_ID[/TD]
[TD="align: center"]Previous_ID[/TD]
[TD="align: center"]Gender [/TD]
[TD="align: center"]Genotype1[/TD]
[TD="align: center"]Genotype2[/TD]
[TD="align: center"]Genotype3[/TD]
[TD="align: center"]Genotype4[/TD]
[TD="bgcolor: #cacaca, align: center"]34[/TD]
[TD="align: center"]51[/TD]
[TD="align: center"]100[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1/0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #cacaca, align: center"]35[/TD]
[TD="align: center"]52[/TD]
[TD="align: center"]100[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #cacaca, align: center"]36[/TD]
[TD="align: center"]53[/TD]
[TD="align: center"]101[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="bgcolor: #cacaca, align: center"]37[/TD]
[TD="align: center"]54[/TD]
[TD="align: center"]102[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="bgcolor: #cacaca, align: center"]38[/TD]
[TD="align: center"]55[/TD]
[TD="align: center"]103[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="bgcolor: #cacaca, align: center"]39[/TD]
[TD="align: center"]56[/TD]
[TD="align: center"]103[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="bgcolor: #cacaca, align: center"]40[/TD]
[TD="align: center"]57[/TD]
[TD="align: center"]103[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
</tbody>
Excel tables to the web >> Excel Jeanie HTML 4
The Database I have is expanding, with additional datasheets being added to it. At the moment I have about 4400 rows by 280 columns. I’m happy to re-run the analysis everytime for an 'Updated_Master_file'.
I would really appreciate if someone could help me with this, or could point me in the right direction.
Thank you
Steve.
