Viewing 23 posts - 1 through 23 (of 23 total)
  • Excel voodoo required!
  • seosamh77
    Free Member

    I’ve an excel sheet, like a master sheet with loads of different information in it.

    But I want to create a small subset of the data into 1 spreadsheet.

    ie Existing sheet is comething like:

    column A,…..,Column R, Column S, …..

    Client A,…..,Value 1, no1, …..
    Client A,…..,Value 1, no2, …..
    Client A,…..,Value 1, no2, …..
    Client A,…..,Value 2, no3, …..
    Client A,…..,Value 1, no1, …..
    Client A,…..,Value 1, no6, …..
    Client A,…..,Value 2, no4, …..
    Client A,…..,Value 1, no1, …..
    Client A,…..,Value 1, no8, …..

    Every client will be something like that:

    Now I want to turn this into:

    “Column A”, “Column B”, “Column C”
    “Client A,” “7 x Value 1, 2 x Value 2”, “No1, No2, No3, No6, No8(don’t want to know how many of each)”
    “client B” etc etc
    “client C” etc etc
    etc
    etc

    Any excel voodoo witch doctors got a quick way of doing this? i’ve got 5000 rows, dont fancy doing this manually! 😆

    Help would be much appreciated! (excel 2003)

    dissonance
    Full Member

    Pivot table might do it although I never used them much. I would go for some VBA personally.
    How often is it being done though?
    If a one off would probably just sort by column a and then b and copy and paste/write a formula if needed to adjust.
    Depending on whether all clients will have all records.

    seosamh77
    Free Member

    Just need to do it the once, so I can then start building on this manually and fill out infomation I know/will be sourcing..

    Fresh Goods Friday 696: The Middling Edition

    Fresh Goods Friday 696: The Middlin...
    Latest Singletrack Videos
    kelron
    Free Member

    So you want to know how many times ‘value 1’ and ‘value 2’ appear for each client, then have a list of which of ‘no1” ‘no2’ etc appear for that client?

    seosamh77
    Free Member

    aye pretty much kelron.

    leffeboy
    Full Member

    That’s what it looks like. Shouldn’t need VBA for the first part. The last part is trickier though. Need to think about that

    Mmm, the difficulty is the outputting as strings with unkown number of members. Not quite so obvious

    kelron
    Free Member

    How big is the range of possible values for each client? If it’s limited you can simplify things by using a column for each.

    seosamh77
    Free Member

    third column, theoretical total could be 32 values, which will be like 1,3,5,6,7,23,404,412 etc

    I’d need idiot level instruction on how to use VBA and the likes btw.

    RobHilton
    Free Member

    “Something like” is too vague. Put together a sample workbook of data (covering all possible variations) *and* the exact output required for best results 🙂

    I don’t have time to look at this now, but if you don’t get what you’re after by tonight I’m fully expecting to not be able to sleep yet again, so I can do it then.

    Emailer in profile.

    leffeboy
    Full Member

    For the VBA it wouldn’t be too bad to write something for you but you would be better avoiding that as it gets messy. I would imagine that the best way would be to use a pivottable for each pair of columns (e.g. Col A+B, Col A+C etc) but the difficulty is then creating a single string output. You can use concatenate and transpose together but that is also not so much fun

    Can you create a sample data set with a sample of what you want out? That would make it much easier as at the moment it is a little vague as @RobHilton says

    seosamh77
    Free Member

    Yes, I’ll create a couple of files, give me a wee bit and i’ll upload.

    dissonance
    Full Member

    For the VBA it wouldn’t be too bad to write something for you

    Yeah testing it would be fun. I am also confused about the target format.
    For a one off job and someone who doesnt think writing code is the solution to most problems. I suspect I would end up sorting either by client or value (depending on numbers of each) and then drop each into its own sheet and do a vlookup or similar from there.

    seosamh77
    Free Member

    Here’s a quick sample file(info striped out), Basically I want to distill columns a, b, c in to what g,h,i look like(Or something like that, I could live with individual columns, I could probably just export as a csv and quickly merge those together manually and reimport to excel. important point is 1 client, 1 row.)

    https://wetransfer.com/downloads/fa0db96de14b3b0ea585ae888241109c20171018145508/a8e41982a611f05a5de099aa13608eb720171018145508/503ba2

    leffeboy
    Full Member

    One client per row is just a pivot table. You might need one per column to match. Will go play once finished here

    mogrim
    Full Member

    Pivot table would get close – select the first three columns, then convert to table (in the ribbon). Insert tab -> create pivot table. In the new sheet select order_bill_name and code as your rows, type for the columns, and order_bill_name as the count for the columns.

    Not sure if the link will work, but:
    https://wetransfer.com/downloads/6bdd189837285ec607d32a7bfa92f22d20171018151444/a050cabcaf0a618ca5d8d2693bbc218e20171018151444/5fad4e?utm_campaign=WT_email_tracking&utm_content=general&utm_medium=download_button&utm_source=notify_recipient_email

    seosamh77
    Free Member

    tbh that’ll do the trick mogrim, now I see the info in that format, and not just how I imagined it, that’s probably better.

    Great stuff..

    That’ll do nicely, I’ll see if i can get that working in excel 2003

    Cheers again everyone. Might be back if I can’t get this working.

    twicewithchips
    Free Member

    Pivot Table.

    As per mogrim, but with order_bill_name, code and type (in that order) as the rows.
    Values should be count of type
    remove subtotals (right click -> field settings) from order_bill_name and code.

    but, I notice that you sent an xls (old versions may differ in pivot table functionality).

    EDIT: yeah – pretty much wot he did while I was typing.

    mogrim
    Full Member

    As per mogrim, but with order_bill_name, code and type (in that order) as the rows.

    Move around as desired 🙂

    One thing you may not know: double click on any value in the pivot table will open up a new sheet with the source values.

    seosamh77
    Free Member

    cool, cheers again Mogrim.

    twice, yes, that’s why I said i might be back! 😆 Mogrims file seems to be opening fine though.

    leffeboy
    Full Member

    Same idea but with formulae to get some text out

    https://we.tl/oCMJ5O6kMv

    leffeboy
    Full Member

    And for fun
    https://we.tl/omADwRxrTr

    Note that this is the ‘no code’ version. If you were using Excel 2016 or VBA then it gets cleaner. It looks a bit horrible but everything is copy and paste to expand it out.

    Note also that in Sheet3 the formulae in columns AI and AJ are ever so slightly different.

    seosamh77
    Free Member

    cool, will take a look at them tomorrow, no excelly type program on my house pc. cheers!

    seosamh77
    Free Member

    got this working, cheers all. and thanks for the intro to pivot tables, pretty easy now i understand what they are! 🙂

Viewing 23 posts - 1 through 23 (of 23 total)

The topic ‘Excel voodoo required!’ is closed to new replies.