+ Reply to Thread
Results 1 to 5 of 5

Skipping values in a matrix using Index

  1. #1
    Registered User
    Join Date
    08-20-2018
    Location
    Mass, USA
    MS-Off Ver
    MS365 version 2303
    Posts
    28

    Question Skipping values in a matrix using Index

    I attempted to attach a spreadsheet, but it wouldn't work.
    The best I can do is provide the screen shot below.

    Example image.jpg

    This is a difficult problem to explain using only words, but I will do my best.
    I have Column Q which shows a material, and then Columns R-AB showing the sizes that the material is available in.
    There are some blank values in Columns R-AB because not all materials are available in all sizes.
    That leaves me with a data set that looks something like this:

    T1001 21 22
    T1002 22 24 25
    T1003 30

    I want to convert this data so that everything is listed in two columns vertically in AD and AE, like below:

    T1001 21
    T1001 22
    T1002 22
    T1002 24
    T1002 25
    T1003 30

    I'm trying to use INDEX, but I'm hitting a wall. Any ideas?
    Last edited by Ian.Lance; 04-17-2020 at 08:08 AM.

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,446

    Re: Skipping values in a matrix using Index

    Administrative note

    Welcome to the forum

    in your haste to solve your problem, you probably missed the yellow banner advising how to get answers faster by posting a sheet ?

    Please take a moment to read it and attach a sheet accordingly.

    Thanks you for helping us help you

  3. #3
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Skipping values in a matrix using Index

    In the interim, based on screenshot, and assuming first row of data is row 5, then using old fashioned formulas ...

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    the use of calc in AF is really just to avoid repeating the same calc in both AD & AE calcs; you could equally use INDIRECT (with AF) but it would become volatile as a result.

  4. #4
    Registered User
    Join Date
    08-20-2018
    Location
    Mass, USA
    MS-Off Ver
    MS365 version 2303
    Posts
    28

    Re: Skipping values in a matrix using Index

    The website would not allow me to attach the spreadsheet. I'm not sure why.

  5. #5
    Registered User
    Join Date
    08-20-2018
    Location
    Mass, USA
    MS-Off Ver
    MS365 version 2303
    Posts
    28

    Re: Skipping values in a matrix using Index

    Fantastic, this worked perfectly! Thanks!

+ 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. Replies: 3
    Last Post: 12-09-2019, 12:44 PM
  2. Index Match (multiple criteria) Skipping Duplicates & Returning Only Unique Values
    By johnwilliamboyle in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 09-05-2019, 12:09 PM
  3. Replies: 10
    Last Post: 01-01-2019, 12:49 PM
  4. Matrix Multiplication in VBA and storing values in another matrix
    By anwid in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-06-2018, 06:23 AM
  5. Clustering Matrix values- how to sort and index?
    By simz92 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-16-2017, 07:13 PM
  6. VBA INDEX [array] formula skipping lines when dragged down
    By KAP123 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-01-2016, 12:47 PM
  7. Replies: 2
    Last Post: 11-05-2014, 03:41 AM

Tags for this Thread

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