+ Reply to Thread
Results 1 to 6 of 6

Analysing a list and creating a chart

  1. #1
    Registered User
    Join Date
    01-12-2020
    Location
    Philadelphia, PA
    MS-Off Ver
    2016
    Posts
    97

    Analysing a list and creating a chart

    Hi, all!

    So what I want to do is look at a list of numbers or letters (columns A-E in my example), and then put an "x" in a chart (columns G-K) that would show me how often and where each letter appeared in each list. Ideally, the chart would be vertical, as shown, but I could modify it if need be.

    A letter would never appear more than once in each list. And the list of letters would always be in alphabetical order, if that helps.

    Thanks!
    Attached Files Attached Files

  2. #2
    Spammer
    Join Date
    10-23-2012
    Location
    Adelaide, Australia
    MS-Off Ver
    Excel 2003, Office 365
    Posts
    1,237

    Re: Analysing a list and creating a chart

    something like this?

    =IF(ISERROR(HLOOKUP($G3,$B$3:$E$3,1,FALSE)),"","x") for each list. Then drag down for each character you are looking for.
    Attached Files Attached Files
    Last edited by Croweater; 05-17-2020 at 12:41 AM.

  3. #3
    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,369

    Re: Analysing a list and creating a chart

    Sorry for off-topic interjection:

    Although there is no official rule regarding this behaviour, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.

    I'm sure you understand, and we look forward to seeing you post your formulas/macros in your posts for the searching benefit of all.
    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.

  4. #4
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Analysing a list and creating a chart

    Here is an alternative that will allow you to copy the formula across and down without having to change the list range each time.

    =IF(ISERROR(MATCH($G3,INDEX($B$3:$E$6,H$2,0),0)),"","x")
    Cheers
    Andy
    www.andypope.info

  5. #5
    Registered User
    Join Date
    01-12-2020
    Location
    Philadelphia, PA
    MS-Off Ver
    2016
    Posts
    97

    Re: Analysing a list and creating a chart

    Thank you both for your help. While Andy's seems easier, when I applied it to my spreadsheet, I couldn't get it to work. I just got "0". I was able to get Croweater's to work for me, though.

    Either way, I now have insights into ISERROR and MATCH and INDEX that I didn't know existed before. I guess that's the purpose of the site...

    Thanks again to both!

  6. #6
    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,369

    Re: Analysing a list and creating a chart

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

+ 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] Creating a Top 3 list per year (Pivot Chart)
    By neil40 in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 10-24-2019, 07:05 PM
  2. [SOLVED] Creating a Dynamic List for Chart
    By lovecolorado in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-21-2019, 05:31 PM
  3. Analysing data based on change range of data including creating charts
    By EagleInsight in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-20-2014, 11:18 PM
  4. Creating Chart from drop down list
    By lsm33000 in forum Excel Charting & Pivots
    Replies: 10
    Last Post: 09-12-2013, 04:47 PM
  5. Replies: 1
    Last Post: 02-04-2013, 03:01 AM
  6. Creating a chart of a list of Dates
    By cakonopka in forum Excel General
    Replies: 4
    Last Post: 04-08-2007, 06:47 AM
  7. Analysing a list of dates
    By matpj in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-28-2006, 11:25 AM

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