I have data in a database field that contains quite a lot of duplication i.e. 1,1,1,2,2,2,2,3,3,3,3,4,4,4,4 and I would like to reduce this to 1,2,3,4.
A spreadsheet can do this for a column of data using a filter, but I don’t think I can get the data into columns (only rows) nor can I create a filter for the first row and then duplicate it across multiple rows – I would need to do about 1600 rows.
Anybody got a handy hint how I can achieve this with the minimum fuss?

what software are you using?

Access you could just ‘group by’ for example, Excel there are a whole host of ways (Countif or pivot tables)

If it’s in a database:
select distinct fieldname from table

Depending on the database software you may also be able to:
select distinct fieldname into newtable from tablename

C

Database is FileMaker (pretty powerful) but I can easily export in many formats to carry out the work in a spreadsheet or other app.

If you can get the data into rows in Excel, copy and paste it into columns using Paste Special with the Transpose box ticked. Then you can filter to unique values. To do all the columns you could probably record it as a Macro but I don’t have a copy of Excel handy to check.

Does this help?

http://support.microsoft.com/kb/262277

EDIT: I don’t think it does re-reading OP

you back up the table, group the values then write the table from this query (or rename the table and create a new table with the original name)

the above could get complex if you have a key on this table rather than it being for lookup purposes hence the backing up first

If you do group them, just export the average, that’ll be the value you want.