+ Reply to Thread
Results 1 to 13 of 13

Returning Occurrences

  1. #1
    Registered User
    Join Date
    06-16-2020
    Location
    Dubuque, IA
    MS-Off Ver
    Excel 2016
    Posts
    8

    Returning Occurrences

    Hello Again,

    I am sure this is way easier than I am making it. I am looking for the following formula:

    I have mainly blanks in columns with wording in a few. I need to bring back the wording in occurrence.

    Column 1 through 10:

    Blank, Blank, wording 1, blank, blank, blank, wording 2, blank, blank, wording 3

    I need to show the following:

    Wording 1
    Wording 2
    Wording 3

    and that is it

    Thanks!

  2. #2
    Valued Forum Contributor
    Join Date
    04-01-2015
    Location
    The Netherlands
    MS-Off Ver
    2003/2007/2010/2016/office 365
    Posts
    880

    Re: Returning Occurrences

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    06-16-2020
    Location
    Dubuque, IA
    MS-Off Ver
    Excel 2016
    Posts
    8

    Re: Returning Occurrences

    Thanks, but when I put that in it returns a blank. I evaluated the formula and it gets to the row(a1) and it comes back with REF.

  4. #4
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Returning Occurrences

    Please read the yellow banner at the top of this page on how to attach a file. I am sure this can be resolved using Power Query and the Unpivot function
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  5. #5
    Valued Forum Contributor
    Join Date
    04-01-2015
    Location
    The Netherlands
    MS-Off Ver
    2003/2007/2010/2016/office 365
    Posts
    880

    Re: Returning Occurrences

    It is an array formula and must be closed with Ctrl+Shift+Enter

    Otherwise, see the big yellow banner at the top

  6. #6
    Registered User
    Join Date
    06-16-2020
    Location
    Dubuque, IA
    MS-Off Ver
    Excel 2016
    Posts
    8

    Re: Returning Occurrences

    I have attached the spreadsheet. I technically need it for 2 rows (2 and 3) and it is much larger than the 10 in the sample. I have put the formula in C6 and C7. Thanks a ton for the help
    Attached Files Attached Files

  7. #7
    Valued Forum Contributor
    Join Date
    04-01-2015
    Location
    The Netherlands
    MS-Off Ver
    2003/2007/2010/2016/office 365
    Posts
    880

    Re: Returning Occurrences

    I have no idea what the file has to do with the question. And what the expected outcome is. Also adjust your profile so that we know which version (s) of Excel you are using.

  8. #8
    Registered User
    Join Date
    06-16-2020
    Location
    Dubuque, IA
    MS-Off Ver
    Excel 2016
    Posts
    8

    Re: Returning Occurrences

    There are blanks all the way up until LE and LZ. I would want to pull what is said in both of those to columns

  9. #9
    Valued Forum Contributor
    Join Date
    04-01-2015
    Location
    The Netherlands
    MS-Off Ver
    2003/2007/2010/2016/office 365
    Posts
    880

    Re: Returning Occurrences

    You can also simply answer the questions asked. You apparently have not understood the term sample file either.
    Attached Files Attached Files

  10. #10
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Returning Occurrences

    In the attached sheet, I used Power Query to unpivot the data in the columns through Jan 31. For example purposes, I saw no reason to extend it out further. I put Yes, No and Blanks so you could see how this worked.

    Here is the Mcode.

    Please Login or Register  to view this content.
    Review PQ
    In the attached file
    Click on any cell in the new table
    On the Data Tab, click on Queries & Connections
    In the right window, double click to open Query
    Review PQ steps

    M-code basics:
    - "let" is the start of a query
    - "in" is the closing of a query
    - each transformation step sits in between those 2 lines
    - each step line is ended by a comma, except the last one
    - "Source" is always the first step (Source Data)
    - After "in" you have the last step referenced
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    06-16-2020
    Location
    Dubuque, IA
    MS-Off Ver
    Excel 2016
    Posts
    8

    Re: Returning Occurrences

    Sorry, it is Excel 2016.

  12. #12
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Returning Occurrences

    Quote Originally Posted by ChrisKolle View Post
    Sorry, it is Excel 2016.
    Then please help us help you by changing your profile to reflect this.
    Dave

  13. #13
    Registered User
    Join Date
    06-16-2020
    Location
    Dubuque, IA
    MS-Off Ver
    Excel 2016
    Posts
    8

    Re: Returning Occurrences

    Sounds good, Done
    XL2016

+ 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] making all occurrences of a value match the change made in one of those occurrences
    By GAZZYMAN in forum Excel Programming / VBA / Macros
    Replies: 27
    Last Post: 03-23-2017, 10:51 PM
  2. [SOLVED] Average returning #div/0 and sum/count returning 0
    By Sinnie in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 02-12-2016, 12:01 PM
  3. Looking up multiple occurrences of lookup value, returning corresponding data
    By radioactivity in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-02-2015, 02:22 AM
  4. Replies: 3
    Last Post: 05-03-2012, 04:00 PM
  5. Formula sometimes returning correct answer and sometimes returning 0
    By redimp in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-09-2010, 06:28 AM
  6. count Re-occurrences
    By stevekirk in forum Excel General
    Replies: 15
    Last Post: 10-16-2006, 02:46 AM
  7. # of occurrences
    By gbeard in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-05-2005, 05:06 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