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
    5,994
    Likes (Given)
    3340
    Likes (Received)
    1260

    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
    I'm on the naughty step. Back when I'm good.
    Join Date
    Jan 2013
    Location
    Poole
    Posts
    8,846
    Likes (Given)
    883
    Likes (Received)
    3858
    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.

  3. #3
    TDF Member
    Join Date
    Aug 2015
    Location
    West Midlands
    Posts
    356
    Likes (Given)
    193
    Likes (Received)
    115
    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,901
    Likes (Given)
    3081
    Likes (Received)
    2070
    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
    5,994
    Likes (Given)
    3340
    Likes (Received)
    1260
    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
    356
    Likes (Given)
    193
    Likes (Received)
    115
    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,760
    Likes (Given)
    273
    Likes (Received)
    468
    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
    5,994
    Likes (Given)
    3340
    Likes (Received)
    1260
    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
  •