Hello and welcome to our community! Is this your first visit?
Register
Results 1 to 8 of 8
  1. #1
    Established TDF Member Finless's Avatar
    Join Date
    Dec 2012
    Posts
    6,037
    Likes (Given)
    3382
    Likes (Received)
    1272

    Another Excel Q.

    I have a spreadsheet listing products and other pertinent information on the same row but in a different colum.

    The products are garment and each product/colour/size combination has its own individual row. This means that one product may take up as many as 20+ rows.

    I would like to print the report but it is 54 pages long.

    Does anybody know if it is possible to do a sort by a specific column and automatically remove duplicates? It doesn't really matter which line is kept but, if it makes a difference to the explanation, I would like to keep the f1st row and delete all subsequent lines.

    Fortunately, for me, the product code is in a separate column from all the other information so it is easy to spot duplicates.

    I could go through and delete the lines manually but I don't want to.

    As ever, thanks for looking/replying.

  2. #2
    Established TDF Member Chrisch's Avatar
    Join Date
    Jan 2013
    Location
    Poole
    Posts
    8,918
    Likes (Given)
    900
    Likes (Received)
    3896
    I stopped Excel a while back and use a free product now. (So please check if this works)

    You can group the multiples using the "data - group" facility. Now you can expand or contract each variable. With all variable collapsed (there is a 1 and 2 on the left that opens or closes everything on the whole sheet) it should print only what is visible. You select the rows you want to combine and group them, then collapse it to just the top row.

    Maybe test a single page first.

    I find it very useful to quickly look through the products then open up each section. On my product list they are in categories but it would work fine for products with multiple variables I think. Saves a lot of time looking for something as you just identify the category and then open that bit of the sheet.

    It will be a bit tedious to do but once done might be helpful for you.
    We give 350m a week to the EU. Let's give it to Dido Harding instead.

  3. #3
    TDF Member
    Join Date
    Aug 2015
    Location
    West Midlands
    Posts
    363
    Likes (Given)
    202
    Likes (Received)
    116
    is this what you want? 'remove duplicates' and choose the columns you want involved
    https://www.dropbox.com/s/ie22b8so2n..._1351.mht?dl=0

    btw learned that windows has a 'steps recorder' that lets you record the actions you take ...who knew!?

    best to 'view as slides' otherwise it is quite small!

  4. #4
    Established TDF Member Nickpicks's Avatar
    Join Date
    Dec 2012
    Location
    Dacorum
    Posts
    3,954
    Likes (Given)
    3167
    Likes (Received)
    2112
    Can you use the filter facility to just show the ones you want, then print that.

    If you also only want certain columns, you could use a pivot table to filter only the rows you want.


    But, what you really need is a database.
    The major difference between a thing that might go wrong and a thing that cannot possibly go wrong is that when a thing that cannot possibly go wrong goes wrong it usually turns out to be impossible to get at or repair.

  5. #5
    Established TDF Member Finless's Avatar
    Join Date
    Dec 2012
    Posts
    6,037
    Likes (Given)
    3382
    Likes (Received)
    1272
    Quote Originally Posted by MinimalMayhem View Post
    is this what you want? 'remove duplicates' and choose the columns you want involved
    https://www.dropbox.com/s/ie22b8so2n..._1351.mht?dl=0

    btw learned that windows has a 'steps recorder' that lets you record the actions you take ...who knew!?

    best to 'view as slides' otherwise it is quite small!
    I don't have a DROPBOX account:-

    .mht files can’t be previewed.

  6. #6
    TDF Member
    Join Date
    Aug 2015
    Location
    West Midlands
    Posts
    363
    Likes (Given)
    202
    Likes (Received)
    116
    Quote Originally Posted by Finless View Post
    I don't have a DROPBOX account:-

    .mht files can’t be previewed.
    use direct download.

    or to cut to the chase...type 'duplicates' in the search/help box in the toolbar of excel (assuming you have a recent version)

  7. #7
    Established TDF Member Energy58's Avatar
    Join Date
    Jun 2014
    Location
    London at the moment
    Posts
    1,785
    Likes (Given)
    275
    Likes (Received)
    480
    From the menu bar

    Data - Data Tools (near the end of the menu) - Remove Duplicates

    Gets you a dialog box with obvious options (column names etc.)

    Job done!

  8. #8
    Established TDF Member Finless's Avatar
    Join Date
    Dec 2012
    Posts
    6,037
    Likes (Given)
    3382
    Likes (Received)
    1272
    Thank you all. I went to the SEARCH box at the top of the spreadsheet and, as suggested, typed REMOVE DUPLICATES and well .... it's magic, wizzardry ..... erm ..... it worked. Selected a few options and my 3000 row spreadsheet has been reduced to under 300 rows of data.


 

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •