selective unpivot macro

[k.03a]

Hi all,

I need to build a quick macro but I don't know any VBA...

Basically I have in Sheet 1 towns as lines and dates as columns. For each date and each city I have a status ("ok","warning" and "stopped")

e.g.

1 sept 2 sept 3 sept 4 sept
New York ok ok ok stopped
Tokyo ok ok ok ok
London ok warning ok ok
Paris ok ok ok ok


I would like to have in sheet 2, a summary of only the "warning" or "stopped" towns, in an unpivotted format (for a database). I can't also take the "ok" ones because then I'll have more than a million lines.

e.g.

Town Date Status
New York 4 sept stopped
London 2 sept warning


Any ideas?

Thanks !

 

[k.03a]

I spoke too soon. It works up to about 2200 lines (regardless of the number of columns it seems), but I need about 4800 lines...

Any workarounds?

 

[MickG]

Can you post the file or data its failing on !!!

Mick

 

[k.03a]

I got it. FOr some reason the column count returned 16384 instead of about 100. I changed it manually to include only 100 columns and it works