multiple formats in spreadsheet causing error in pivot table values

[PaddyHannen]

Hi, thanks for reading.

I'm editing several supplied spreadsheets which have been compiled from many horrible locations.
There is a format diffefence in the main sheet (i cant clear it with Shift Ctrl * or format paste)
It is causing my pivot table to return multiple values for the same item. (the data is the same across the range but coming uot in the table over several different lines as separate values)

Is there a way to normalise the formatting or to export the data only into a clean sheet to remove the format difference so the table works properly?

 

[BlueHornet]

Are you sure that it's really "formatting" and not something like merged cells? I've seen merged cells play merry hell with summary type functions, but not formatting. Or maybe I'm just not visualizing the extent, kind or differences in formatting that you're discussing.