Consulting

Results 1 to 14 of 14

Thread: Solved: Excel data organization nightmare

  1. #1

    Solved: Excel data organization nightmare

    hi! I have a difficult data organization problem and I will do my best to explain it so please bear with me. I really hope someone can help me resolve this as i need to do this for work everyday starting tomorrow.

    I receive data organized like so:
    Column A contains numbers ranging from 2000-30000 in random increments in ascending order.
    Column B contains the letters C and P (corresponding to the numbers in column A) in random order.
    Column C contains just some random numbers corresponding to columns A and B.

    e.g.
    Column A Column B-Type Column C
    3000 C 5560
    5000 C 3560
    5400 P 1
    5600 P 1
    5800 P 1
    6000 C 2560
    6000 P 2

    etc. etc. for many thousands of cells.




    How I need the (above) data organized:
    Column A Column B-Type C Column C-Type P
    3000 5560 N/A
    5000 3560 N/A
    5400 N/A 1
    5600 N/A 1
    5800 N/A 1
    6000 2560 2
    etc. etc. for thousands of cells.

    I hope this makes sense.

    I know how to use the "sort" function in excel but it doesn't do this kind of sorting i dont think.

    if someone can please help me i would greatly appreciate this!!! please ask me questions if something does not make sense. thank you!!

  2. #2
    VBAX Mentor anandbohra's Avatar
    Joined
    May 2007
    Location
    Mumbai
    Posts
    313
    Location
    say your data is in Column A, B, C respectively
    as per your example Column A will not change ok
    so now user column D,E,F to get your required output
    so for column D i.e. D1 formula will be =A1 which gives u 3000 (your example)
    now for Col. E i.e. E1 formula will be= =IF(B1="C",C1,"N/A") which gives u 5560
    & for Col F i.e. F1 formula will be = =IF(B1="P",C1,"N/A") which gives u N/A


    copy all formula till u require & u get your desired output

  3. #3
    VBAX Expert
    Joined
    Feb 2005
    Location
    Nanaimo, British Columbia, Cananda
    Posts
    568
    Location
    Hi Illinois! (and Blumfeld0)

    There's a 2 hour time difference. (I'm in PDST) but here's a sample based on your example. It could do a lot more, or different or... depending on what you really need.

    However, I've a feeling this could save you tons o' time! Would your work be willing to pay for that time savings or is it just you trying to get ahead? Either way, computers are supposed to save you time are they not? <g>

    It's midnight:20 here and 2:20 AM where you are, I just finished a show (my work) and I'm heading home for a beer so I may not be able to help you out for your tommorrow morning but post here or email me and I'll be able to help you out - one way or the other, I think.
    Cheers,

    dr

    "Questions, help and advice for free, small projects by donation. large projects by quote"

    http:\\www.ExcelVBA.joellerabu.com

  4. #4
    VBAX Master
    Joined
    Jun 2007
    Location
    East Sussex
    Posts
    1,110
    Location
    Have you thought about a pivot table?
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    Rory- thank you for your response.
    I am not the best excel user so am not too familiar yet with pivot table. if you think this can be done with a pivot table I will absolutely try it and get back to you.


    anandbohra --thank you for your response. i tried what you indicated and it is SO VERY close to working perfectly.
    the only problem is when i have a situation like
    Column A Column B Column C
    6000 C 2560
    6000 P 2
    i.e when column A has two numbers (6000, in this case) that are the same back to back! so frustrating. but I have that a lot in my data.
    so what happens in this case when i try your "if" statements is that I get output like this:
    Column D Column E Column F
    6000 2560 N/A
    6000 N/A 2

    But what i need the output to look like this:

    Column D Column E Column F
    6000 2560 2

    any ideas guys how to correct this?


    thank you for all your help!! very much appreciated!
    Last edited by blumfeld0; 09-06-2007 at 09:31 AM.

  6. #6
    VBAX Mentor anandbohra's Avatar
    Joined
    May 2007
    Location
    Mumbai
    Posts
    313
    Location
    hi blumfeld0
    as suggested by rory the pivot will be best in your case.
    i have attached the small example file
    in sheet1 from column A to C paste your data
    then in sheet pivot - on pivot table right click & click refresh data u get your desired output.
    if not happy then in next post upload your partial data ok

    but i think this will solve your purpose

  7. #7
    i hope I can do this with a pivot table since I am not familiar with this tool.but with your attached example it should make it a lot easier to implement for all my data. if you think of a way to do this with an IF statement or other type of statement please let me know.
    thank you so much!! you all have been a big help

  8. #8
    So I have been able to use pivot table to reproduce your results anandbohra, thank you.
    I have one final question and then this issue will be resolved hopefully. In your example anandbohra, in column A you put two numbers that were the same, namely, 6000.
    what do I do if I have 6000 appearing more than 2 times?-my data has some repeated as much as 10 times! in this case, i find that when i do "sum of marks" it ADDS the "marks" instead of just putting them in a organized fashion. i dont want to add them.

    ex.
    6000 C 2560
    6000 P 2000
    6000 P 2000


    pivot table outputs:
    6000
    C P
    2560 4000


    that is bad.
    thank you again!

  9. #9
    VBAX Newbie
    Joined
    Sep 2007
    Posts
    4
    Location
    Try double clicking the grey cell just over the left colomn of the Pivot Table (in the prior example it was titled No)...if the subtotals are set to automatic change them to none.. hope this helps

  10. #10
    JGobert--thank you for the suggestion. I set the option to "none" but it didn't work. is this surely supposed to work or am i being dumb? this could well be possible.

    thank you! ill keep trying. if anyone thinks of something let me know. very much appreciate it!

  11. #11
    VBAX Master
    Joined
    Jun 2007
    Location
    East Sussex
    Posts
    1,110
    Location
    A pivot table will always perform an aggregation on your data. What would you want the data to look like?
    Regards,
    Rory

    Microsoft MVP - Excel

  12. #12
    after reading in detail about pivot tables i have organized my data the way i wanted to. thanks for the good idea guys!

  13. #13
    VBAX Mentor anandbohra's Avatar
    Joined
    May 2007
    Location
    Mumbai
    Posts
    313
    Location
    blumfeld0

    this small change will give you exactly what u want







    Sum of Marks No Value Total 1000 P 2458 2000 C 2460 3000 C 2160 P 2200 4000 C 2450 P 2300 5000 C 2468 6000 C 2560 P 4000

  14. #14
    thanks anandbohra. youre a genius! time to do a lot of dragging and organizing.

    blumfeld0

Posting Permissions

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