+ Reply to Thread
Results 1 to 8 of 8

Help with collating and sorting data from multiple columns

  1. #1
    Registered User
    Join Date
    01-29-2021
    Location
    California, USA
    MS-Off Ver
    Microsoft Home and Student 2019
    Posts
    3

    Help with collating and sorting data from multiple columns

    Hello all, I am confused how to collate or tabulate the results in this data set. Any help would be appreciated. I think this can be accomplished by using a formula but every effort has failed. I have given up and am asking for help.

    This is for a Roll Playing Game with Star Wars type firearms. The Damage gets smaller with Range, as does the Penetration. I am looking for a way to tabulate the data in table B:3 to D:31. Then reproduce it in F:20 to K:21. I highlighted the applicable data in red (column B) and Orange (column C and D).

    To explain the data in depth, B:3 to D:31 is a formulated data set. F:21 is the Damage 5 and penetration 2, from ranges of 0-5m. G:21 is where the damage is 4, pen 2. at ranges of 5-165m. H:21 ranges or 165-275 dam 3, 2 pen. So on so forth... Once the Pen is equal to the Dam, Nil should be displayed.
    Attached Files Attached Files
    Last edited by Milano1000; 01-30-2021 at 01:28 PM. Reason: It was suggested to re title the Title

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,401

    Re: How to compile this data? I am not sure how to go about this

    Welcome to the forum.

    It is easy to find out your Excel version. Look at the splash screen when the software loads or go to File | Account. Please update your forum profile with this information. Thanks.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,899

    Re: How to compile this data? I am not sure how to go about this

    Once you get it into F20:K21... will you be using the numbers in any further calculations... or is that the end-point? I ask because doing anything (other than reading) cells containing two values separated by something (a "/" in your case) is very messy and BEST avoided.
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,899

    Re: Summarize two sets of data in one cell

    Hi. Forum Rule 1 is very short. It states: "Title must briefly summarize your request."

    I didn't like your title... very vague... but I'm not entirely sure if my suggestion is much better!! The point is... give it a little thought in line with the words below....

    A GOOD thread title is the sort of thing you would use as a search term on Google*. Your chosen title would produce a squillion useless hits. In future, please take a bit more time to think of a descriptive title that would be "Google-friendly". Many people search the forum answers looking for help and your very general title wouldn't help them at all.

    Since you are a new user of this site, on this occasion, I have changed it for you.

    However, if you continue to use weak titles, you can expect to have your thread BLOCKED until you change it yourself. If that happens, to edit the thread title, open the original post, click "Edit Post" (bottom right) to access the area where you can edit your title.

    *Other search engines are available!!!

    Regards,

    Glenn.

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,899

    Re: Summarize two sets of data in one cell

    I have taken a quick look at this and can not see the logic behind it. Somethimes the desired value from column B coincides with a change in the value in column C... somethimes with a change on the NEXT ROW in column C (see black filled cells)... or somethimes with a change in the value in the next row of column D (green cell).

    And then... somethimes the value in column D changes and it has no effect on anything (orange cells).

    What is the logic, 'cos I can't see any...
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    01-29-2021
    Location
    California, USA
    MS-Off Ver
    Microsoft Home and Student 2019
    Posts
    3

    Re: Summarize two sets of data in one cell

    I truly apologize for messing up both my signature and title. I do not use computers nor Excel professionally, thus I am at a handicap. Please bear with me. I am trying to be succinct with my wording and avoid confusion. Please excuse me, I am trying.

    Firstly, Yes. The table F:21 to K:21 is the end point.

    The logic to it is that it is just a set of math mathematical formulas that represent data points. These data points coincide with the Range Band in column B. To clarify, the columns C and D are two separate formulas. So thus, (in row 7) at 145m the data should be eventually read as "4 / 2". As in a reference to G:20 and G:21. At 900m (row 27) should eventually read "1 / nil" as in K:20 and K:21.

    What I am trying to accomplish is take the data set of B:3 to D:31 and have Excel automatically Create the table in cells F:20 to K:21.

    I appreciate you helping me with this. I am trying to be poignant.

  7. #7
    Registered User
    Join Date
    01-29-2021
    Location
    California, USA
    MS-Off Ver
    Microsoft Home and Student 2019
    Posts
    3

    Re: Help with collating and sorting data from multiple columns

    I feel that I didn't explain myself well. I hope I didn't loose any potential help

  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,899

    Re: Help with collating and sorting data from multiple columns

    You have not explained yourself well.


    Explain WHY your expected answers are correct..

    Do you want formulae for rows 20 and row 21? I have assumed thta it is ONLY for row 21... as I do not know what rules are used for selection of the range boundaries in row 20.

    REMEMBER: You know what you want. We look at this for a few seconds. If it is badly explained or the pattern is not obvious... we can not read what's inside your head. YOU need to explain.


    When you say 5/165 and 165/275... which category is 165 in the first or the second? It has to be either 5/164 and 165/275 or 5/165 and 166/275. It CAN NOT be both!!!


    What I can see, for row 21....

    0/5 biggest value in C plus only value in D

    5/165 biggest value in C plus only value in D

    165/275 smallest value in C and the only value in D

    275/350 biggest (or smallest!!) value in C and Nil... because for SOME (a percentage? any? all?) of the values D=C

    350/575 smallest value in C and nil (now all cases D>D)

    Excel is logical. It works on RULES. Your rules are either illofgcal, or you have not explained what they are. Until we know what the rules are.... any attempt at help is impossible.

+ 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] Compile data into one
    By paradise2sr in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 09-28-2020, 09:22 PM
  2. Filtering Data Based on Date Value in Cell and compile the Data for Analysis
    By Rajeshkumar R in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-11-2019, 08:31 AM
  3. [SOLVED] Need help to compile data
    By Imrank in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 10-14-2014, 02:18 AM
  4. Replies: 16
    Last Post: 11-05-2013, 06:48 AM
  5. data compile
    By OldManKidd in forum Excel General
    Replies: 3
    Last Post: 08-29-2010, 02:13 AM
  6. Compile data
    By traderindia in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-03-2010, 02:33 AM
  7. VBAProject name compile error, not defined at compile time
    By Matthew Dodds in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-13-2005, 03:20 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