+ Reply to Thread
Results 1 to 4 of 4

Chart colored cells on a separate tab to display conflicts

  1. #1
    Registered User
    Join Date
    07-12-2019
    Location
    Rock Hill, South Carolina
    MS-Off Ver
    Office 365
    Posts
    28

    Chart colored cells on a separate tab to display conflicts

    Hi All!

    For a project, I have to do a language check on a set of words to determine if they have any actual meaning in other languages. I've been able to do that, but now I have to find a way to neatly present the data. And I used conditional formatting to highlight the words that are actual translations. But I can't figure out how to chart the results. Here is an example of what I am working with:

    Capture.JPG


    So what i need is for the words with actual translations to populate on another tab. Then under the name the language that has the translation would populate, then beside that the translation would appear. And I need that to happen for each word that has a translation (which are highlighted in red).

    I imagine it to look something like this:
    Capture2.JPG

    But, it needs to ignore the words that have no red cells in the row.

    Anyone have any ideas? I thought that maybe this could be a formula or a function, but I am wondering if it would require VBA.

    I've attached a workbook for testing!
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    04-15-2013
    Location
    Melbourne
    MS-Off Ver
    Office 365
    Posts
    62

    Re: Chart colored cells on a separate tab to display conflicts

    please see attached workbook.
    Attached Files Attached Files
    Row row row your boat
    Gently down the stream

  3. #3
    Registered User
    Join Date
    07-12-2019
    Location
    Rock Hill, South Carolina
    MS-Off Ver
    Office 365
    Posts
    28

    Re: Chart colored cells on a separate tab to display conflicts

    Thank you so much! But would you mind explaining the formula a bit? Explaining how it works?

  4. #4
    Registered User
    Join Date
    04-15-2013
    Location
    Melbourne
    MS-Off Ver
    Office 365
    Posts
    62

    Re: Chart colored cells on a separate tab to display conflicts

    ok,

    Let me explain this formula to you from the inside out.

    Part 1:
    Please Login or Register  to view this content.
    this part will generage record, with the table header.
    Please Login or Register  to view this content.
    this is filter condition for FILTER function, ('Language Check'!$B$1:$B$125="Prefix") means including table header.
    Attachment 697826

    Part 2:
    Please Login or Register  to view this content.
    transpose function will convert the above filtered multiple columns array into a 2 columns array.
    Attachment 697830

    Part 3:
    Please Login or Register  to view this content.
    get record without header.

    Part 4:
    use FILTER function again, take the 2 columns array get from above "Part 2" as new data source, this part
    Please Login or Register  to view this content.
    as the filter criteria.

    Attachment 697831

+ 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. Compare a cell to the contents of two columns, output conflicts to a separate cell
    By Solaire in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-14-2019, 12:37 PM
  2. Replies: 1
    Last Post: 03-30-2018, 11:42 AM
  3. Replies: 4
    Last Post: 01-19-2016, 10:24 PM
  4. [SOLVED] Save all worksheets in a folder (incl subfolders) as separate files (w/o name conflicts)
    By 1819 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 06-02-2015, 08:02 PM
  5. [SOLVED] how do I display two separate pie charts on the same chart
    By Janie in forum Excel Charting & Pivots
    Replies: 7
    Last Post: 10-23-2014, 03:04 AM
  6. Replies: 1
    Last Post: 11-09-2013, 02:43 AM
  7. Have blocks of blue colored cells ... want them surrounded by lavender-colored cells
    By rocket1406 in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 07-01-2007, 03:59 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