 You don't need to be an 'investor' to invest in Singletrack: 6 days left: 95% of target - Find out more
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)
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.
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..
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?
aye pretty much kelron.
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
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.
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.
"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.
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
Yes, I'll create a couple of files, give me a wee bit and i'll upload.
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.
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.)
One client per row is just a pivot table. You might need one per column to match. Will go play once finished here
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
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.
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.
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.
cool, cheers again Mogrim.
twice, yes, that's why I said i might be back! 😆 Mogrims file seems to be opening fine though.
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.
cool, will take a look at them tomorrow, no excelly type program on my house pc. cheers!
got this working, cheers all. and thanks for the intro to pivot tables, pretty easy now i understand what they are! 🙂