+ Reply to Thread
Results 1 to 5 of 5

Need help with consolidating and arranging data.

  1. #1
    Registered User
    Join Date
    01-12-2013
    Location
    California, USA
    MS-Off Ver
    Excel 2010
    Posts
    8

    Need help with consolidating and arranging data.

    Hi,

    I am facing an interesting Excel 2010 issue which only becomes worse in the dim light of my poor Excel skills. I have no skill with VBA either, so please bear with me. Here is the problem I am working with:

    1. I have a regularly updating data source which exports into two separate .csv files. After some struggle, I was able to figure out how to turn those .cvs files into one .xlsx file with two sheets. The data on both sheets is related by an ID number, but the ID number is categorized with a different title in each individual sheet. Here is an example:

    Sheet 1
    PrimaryID
    1
    2
    3
    4
    5

    Sheet 2
    RefID
    1
    2
    3
    4
    5

    2. On almost every occasion, there are multiple rows related to the "ID" field in Sheet 1. There is also no specific set of "service types" (well there is a set of about 30 of them which I am happy to plug into a formula or sheet template, if that s what I need to do), so the data in and related to the "Service" Column changes. Example attached here example.xlsx.

    My goal is to consolidate the data from both sheets into a third sheet, further consolidate the duplicate ID information into single line (creating new columns for each multiple item, displaying the Notes below each service, and applying a unique qty/rate for each new "Service" column created), and preferably automate the process as I will be populating Sheet 1 and 2 with new data daily. I added an estimate of what Sheet 3 may look like to my attachment.

    The greater issue is that I am attempting to use sheet 3 as a daily MS Access update file. I assumed it would be less messy to arrange the data before it got to Access, but if there is a better way to do it in Access, I am also excited to hear about those options.

    After about 16 hours of searching google, forums, and youtube, I am at a complete loss. I'm sure not knowing how to word my search has much to do with that though. Any help anyone is willing to spare is appreciated. I apologize for my lack of knowledge It seems like a big task to me, but after doing so much reading, it also seems like there may be a relatively straightforward formula or macro.

    Thank you for taking the time to read,

    =)
    Last edited by xiiro; 01-13-2013 at 04:51 PM. Reason: attachment in the wrong place.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Big Noob With Complex Excel issue, Umm rearranging data (I guess)?

    Hi xiiro and welcome to the forum

    We would love to help you with you're question, but 1st, please rename your thread to something more meaningful, that actually describes your problem. lots of contributors will look at a thread title, and if it is of interest to them, or falls within their area of expertese, they might only open those threads. also, searching for "help" will not turn up too many results that will benifit you, sorry. Look at it this way...if you typed that title into google, what would you expect to get back?
    To change a Title on your post, click EDIT on you're 1st post, then Go Advanced and change your title
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    01-12-2013
    Location
    California, USA
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Big Noob With Complex Excel issue, Umm rearranging data (I guess)?

    Quote Originally Posted by FDibbins View Post
    Hi xiiro and welcome to the forum

    We would love to help you with you're question, but 1st, please rename your thread to something more meaningful, that actually describes your problem. lots of contributors will look at a thread title, and if it is of interest to them, or falls within their area of expertese, they might only open those threads. also, searching for "help" will not turn up too many results that will benifit you, sorry. Look at it this way...if you typed that title into google, what would you expect to get back?
    To change a Title on your post, click EDIT on you're 1st post, then Go Advanced and change your title
    Thanks for the tip, I have gone ahead and changed the title. As explained in the original post however, I'm not too sure how to best categorize my issue. Hopefully the new title will not be misleading somehow.

  4. #4
    Registered User
    Join Date
    01-12-2013
    Location
    California, USA
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Need help with consolidating and arranging data.

    After another day and a half of research I think I know how to better word what I am looking to do.

    is it possible to create a formula or macro that executes the following?

    COPY - the data from "Sheet 1" and merge it with the items on "Sheet 2" which share the same ID.
    IF - the word "Miles" occurs in column "B" of "Sheet 2".
    THEN - copy the data (not the sum, but the actual data) in that same row, to the "Miles Column" on "Sheet 3".

    My project is composed of two sheets with the following headers:

    Sheet 1 - ID, Service, Quantity, Description, Rate

    Sheet 2 - ID, Date, InvoiceNumber, Memo

    The data in Sheet 1 often times has multiple rows using the same ID, but in Sheet 2 each ID only shows up once.

    I am attempting to turn the merged list of data from Sheets 1 and 2 into a table which displays all data related to each individual ID into one row.

    I hope this is a better description of my help request.
    Last edited by xiiro; 01-14-2013 at 09:22 PM.

  5. #5
    Registered User
    Join Date
    01-12-2013
    Location
    California, USA
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Need help with consolidating and arranging data.

    After much research I was able to resolve this by using a formula. Here is the thread where I was able to get help fleshing out that formula.

    http://www.excelforum.com/excel-form...hing-data.html

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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