+ Reply to Thread
Results 1 to 15 of 15

Pull spefic data, sort & average it and put into seperate tables

  1. #1
    Registered User
    Join Date
    08-25-2011
    Location
    Preston, England
    MS-Off Ver
    2010
    Posts
    24

    Pull spefic data, sort & average it and put into seperate tables

    Sheet1.jpgSheet1.jpg

    I have (what I think) is a complicated problem here! I've always been successful with my problems on here so I'm hopeful! I want to use the data in the attached sheet, extract parts of it and organise it into something much more simple. Probably into another sheet within the same workbook.

    I only want to take data from parts (customer or supplier side) which have not got anything next to it in either the supplier or customer side. The first example of this starts at row 21 where the supplier side has details but there is nothing in the customer side.

    I would need 3 "tables" for each the supplier and customer side, organised into location. So 1 for North, 1 for South and 1 for Mids for both customer and supplier side - 6 in total. Within those tables I need it organising into products, whatever that might be. I'd then like it organising into monthly period's with an average price for each month.

    My aim is to have a sheet that at a glance tells me you need to buy/sell x number of tonnes for these months of this product in the north/south/mids.

    I know I'm asking a lot but if there is anyone who thinks they can help me set it up or knows of anybody that could offer this service I'd be massively greatful. I understand that this sort of service might not come for nothing.

    Thanks,

    Ant
    Last edited by antmcg12; 11-05-2014 at 08:17 AM.

  2. #2
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,252

    Re: Complicated spreadsheet help!

    Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

    Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

    To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

    (This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

  3. #3
    Registered User
    Join Date
    08-25-2011
    Location
    Preston, England
    MS-Off Ver
    2010
    Posts
    24

    Re: Complicated spreadsheet help!

    I've edited it - sorry

  4. #4
    Forum Expert boopathiraja's Avatar
    Join Date
    07-12-2013
    Location
    Coimbatore,TamilNadu, India
    MS-Off Ver
    Excel 2007, 2013, 2016, 365
    Posts
    1,455

    Re: Pull spefic data, sort & average it and put into seperate tables

    Hi, Can you post a sample of your workbook ?
    Click just below left if it helps, Booşathì

  5. #5
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,252

    Re: Pull spefic data, sort & average it and put into seperate tables

    Thanks for changing the title.

    It's really hard to visualise what you're trying top achieve. The image attachments don't work for me, and I wouldn't be able to work with them, anyway: really need a sample workbook to look at:



    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic

  6. #6
    Registered User
    Join Date
    08-25-2011
    Location
    Preston, England
    MS-Off Ver
    2010
    Posts
    24

    Re: Pull spefic data, sort & average it and put into seperate tables

    Will do when I get chance. Thanks.

  7. #7
    Registered User
    Join Date
    08-25-2011
    Location
    Preston, England
    MS-Off Ver
    2010
    Posts
    24

    Re: Pull spefic data, sort & average it and put into seperate tables

    NEW FORWARDS SHEET.xlsx

    Did this attachment work??

    It's a very rough mock up of how I want it to be. The main database is in Sheet 1. The sheet named "Lists" contains the info for some drop down menu's in the main sheet. Sheet 3 is the important one. This is where all the outstanding stuff to buy or sell is given in a simple format. I've mocked up the first table from the info in the main sheet. All the figures and details in there are correct as per main sheet. It has average price. If possible, where purchase contract periods overlap by a few months, as in the example, I'd also like to total up the tonnage for the overlapped months and give that separately. Anyway, have a look at it and see what you think and if you can make any sense of it! Thanks, Ant.

  8. #8
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,252

    Re: Pull spefic data, sort & average it and put into seperate tables

    One more question, before I look at your attachment - your profile says you use Excel 2003, but your attachment is in >2007 format. Which version are you actually using?

    (I ask to make sure I don't recommend a solution which uses functionality not available in your Excel version)

  9. #9
    Registered User
    Join Date
    08-25-2011
    Location
    Preston, England
    MS-Off Ver
    2010
    Posts
    24

    Re: Pull spefic data, sort & average it and put into seperate tables

    I think it's actually 2010. It will be used by a few different people though. Nothing older than 2007.

  10. #10
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,252

    Re: Pull spefic data, sort & average it and put into seperate tables

    Attached is a starting point. I've restructured your "Data" sheet slightly, to give each column a unique header, added a helper "Status" column to identify the row status, then created pivot table reports insheet "Report".

    Far from a finished product, but a start you can work with, maybe?
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    08-25-2011
    Location
    Preston, England
    MS-Off Ver
    2010
    Posts
    24

    Re: Pull spefic data, sort & average it and put into seperate tables

    forward contracts test.xlsm

    Looks awesome so far! I've attached another sheet. I deleted some data and added some new but it hasn't updated the pivot tables. Also could you make it pull the "from - to" dates on the both sides. My fault, I forgot to put the dates at the sold side the first time round. Thanks very much for that though! looking brilliant!

  12. #12
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,252

    Re: Pull spefic data, sort & average it and put into seperate tables

    Have you tried refreshing the pivot tables?!

  13. #13
    Registered User
    Join Date
    08-25-2011
    Location
    Preston, England
    MS-Off Ver
    2010
    Posts
    24

    Re: Pull spefic data, sort & average it and put into seperate tables

    Amazingly that worked! Ha. Sorry, I'm not overly familiar with pivot tables. Is it easy enough to have them automatically update? I've been having a bit of a play around with it - making sure the functionality is good. So far so good. One thing I did try doing on an earlier version was having it tot up the number of months in the main data sheet instead of manually working out how many months there are in a given to-from period. I managed to do it it didn't work going from 1 year to another. Some contract periods may only be 1 or 2 months. Some may span 2 years. It would need to include the start at end month so May-Sep would be 5 months. Oct2014-Apr2015 would be 7 months.

    How much in the way of changing things can I do without it being destructive? e.g. can I change column names within the tables or main sheet? And how easy is it if I wanted to add another location (therefore creating another set of pivot tables).

  14. #14
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,252

    Re: Pull spefic data, sort & average it and put into seperate tables

    Some broad questions there. I'd recommend researching pivot tables, so you understand them better (and things like refresh on open options) - Then try things out, to see how they work. When you change a column header, it will change the field name in the pivot table, so the pivot table may need to be adjusted to reflect your change. New values in a field (e.g. a new area) will be included when the pivot table is refreshed.

  15. #15
    Registered User
    Join Date
    08-25-2011
    Location
    Preston, England
    MS-Off Ver
    2010
    Posts
    24

    Re: Pull spefic data, sort & average it and put into seperate tables

    OK. I've created a new location and made a pivot table. I think I've pretty much got it the same as you apart from one part. Where SProduct is meant to be, it says Row Labels and I cant work out how to change it????

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Complicated IF and COUNTIF for a bakery spreadsheet
    By twigdip in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 08-08-2013, 11:37 AM
  2. Complicated Spreadsheet
    By Brodysseous in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-29-2013, 04:48 PM
  3. HELP! Trying to make complicated spreadsheet
    By luketenley in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-15-2012, 10:50 PM
  4. Creating first spreadsheet! Complicated
    By Shannon1981 in forum Excel General
    Replies: 1
    Last Post: 07-15-2011, 05:11 AM
  5. Complicated Spreadsheet Question
    By dgx in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-20-2007, 05:24 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1