Re-arrange/Consolidate Data

[KrisWain320]

Hi all,

Need some help with some code or something at least.

I have two separate output files which have two very different formats.

Format 1:

[TABLE="width: 284"]
<colgroup><col><col span="3"></colgroup><tbody>[TR]
[TD]Name [/TD]
[TD]X [/TD]
[TD]Y [/TD]
[TD]Z [/TD]
[/TR]
[TR]
[TD]R06A821AHE[/TD]
[TD]5286.55[/TD]
[TD]327[/TD]
[TD]1507[/TD]
[/TR]
</tbody>[/TABLE]


this continues down up to 500 different "features" with their co-ordinates.

The other file has a format like this:

[TABLE="width: 225"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]R06A821AHE[/TD]
[TD]X[/TD]
[TD="align: right"]5286.55[/TD]
[/TR]
[TR]
[TD]R06A821AHE[/TD]
[TD]Y[/TD]
[TD="align: right"]327[/TD]
[/TR]
[TR]
[TD]R06A821AHE[/TD]
[TD]Z[/TD]
[TD="align: right"]1507[/TD]
[/TR]
</tbody>[/TABLE]

and comes in long list without any headers.

What i need is some form of code that will take the second format and make it match the first format.

The idea is i'm trying to comparing outputs from format 2 to what they should be in format 1 and highlight any differences between the X, Y and Z coordinate.

Ideally i would like a macro i run on the output file (format 2) that converts it to something similar to format 1. From here i can copy the data into the initial file (format 1) sort by alphabetical order and apply a conditional format so any difference in X, Y or Z are highlighted in red.

end example:
[TABLE="width: 348"]
<colgroup><col><col span="4"></colgroup><tbody>[TR]
[TD]Name [/TD]
[TD]X [/TD]
[TD]Y [/TD]
[TD]Z [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]R06A821AHE[/TD]
[TD]5268.55[/TD]
[TD]327[/TD]
[TD]1507[/TD]
[TD](Orignal Format 1)[/TD]
[/TR]
[TR]
[TD]R06A821AHE[/TD]
[TD]5286.55[/TD]
[TD]327[/TD]
[TD]1507[/TD]
[TD](Format 2 After Change) [/TD]
[/TR]
[TR]
[TD]R06A821AHE[/TD]
[TD]5286.55[/TD]
[TD]327[/TD]
[TD]1507[/TD]
[TD](Formatting if the same) [/TD]
[/TR]
[TR]
[TD]R06A821AHE[/TD]
[TD]5268.65[/TD]
[TD]328[/TD]
[TD]1506[/TD]
[TD](Formatting if different)

[/TD]
[/TR]
</tbody>[/TABLE]


Overall I need some form of macro or If statement that allows me to group all the data in format 2 to match that of format 1. From there i can try and create something that can compare the format 1 and format 2 results together.

Kind Regards
Kris

 

[BGY23]

Try using a pivot table. It will allow you to put col 1 verticaly with col 2 horizontal and values below. Try a sample first.

As for code, not me I'm afraid. But someone will be along who can do that.