+ Reply to Thread
Results 1 to 7 of 7

Index, match, dynamic and connected without sorting helper column non-adjacent index match

  1. #1
    Registered User
    Join Date
    10-23-2013
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    7

    Cool Index, match, dynamic and connected without sorting helper column non-adjacent index match

    Hello All,

    Thanks in advance for the help and sorry if it's a complicated or confusing spreadsheet to look through. I've been searching through the posts to try to answer my questions and have gathered a lot of useful information but now I just need a little bit of help putting it all together. This link is a dead one, but I'm referencing it because he partially answered one of my questions.


    http://www.excelforum.com/excel-form...e-results.html


    Quote Originally Posted by jake.masters View Post
    You will probably need to use Match and Index rather than VLOOKUP, which will always just return the first value. The only way around this is to set a dynamic range, which is more difficult.

    http://www.mrexcel.com/articles/exce...ndex-match.php

    After you match the item, simply add a +1 to the index of the second item and it will pull in the information one cell down.


    Match looks like: =MATCH(Value, array, < > or =)

    Index Looks like: =INDEX(Array, Row #,[column #])

    To use them together, nest match inside of index as the row #.

    =INDEX(A1:A30, MATCH(part number, table of part numbers, 0))

    For the next part number, if it is right below it,

    =INDEX(A1:A30, MATCH(part number, table of part numbers, 0)+1)

    If the second part # is not one cell below it, this will be more complicated, and you should post a sample of your issue.
    MY EXAMPLE FILE
    Index Match If Rows Max Count If Lookup CONFUSED_macro 3.xlsm

    Question #1
    NON-ADJACENT INDEX MATCH +1 LOOPHOLE???
    The incoming funds section of my spreadsheet (tab=OutPut Sheet and cell=A56:A68) is referencing the count if helper columns beginning at B71 and returning the project #'s from the Office_List table on the Transfer Questionaire tab. All of the project numbers have the same OFFICE NAME. I'm able to get the correct COUNT of output in Incoming Funds Project, but they are returning the incorrect project numbers. It is only returning the FIRST project number from the list to which it is refering, whereas I want it to return ALL of the project numbers. The post above refers to a solution, but states that the data must be adjacent to eachother and that if the data are not adjacent, then it gets more complicated and you should post an example to solve this. My data is not next to eachother and it wouldn't be convienient to make it right next to eachtoher each time, so how can I work around this?

    I DO NOT KNOW WHERE THE DATA WILL BE SO HOW DO I GET AROUND THIS LOOPHOLE, IT MIGHT BE CONSTANTLY CHANGING!


    Incoming Funds (Consultants)
    Project Consultant Office Payroll Travel Admin Total
    10001
    10001
    10001
    10001
    10001
    10001
    10001
    10001
    10001
    10042
    FALSE
    FALSE
    FALSE


    Question #2
    CONNECTING WITHOUT SORTING / DYNAMIC / CONNECTED
    Output Sheet first table refers to the project tab and uses index match to pull the information with the use of a helper column. This requires sorting the data. Is there any way that I can pull this information without using the helper column, or with using the helping column but wihtout having to worry about sorting the data in order to do so? I would also prefer the total and sum columns to be right at the end of the data as opposed to far at the bottom (if there were fewer occurences for a particular office). I am also worried about the user forgetting to add more columns as the list grows (for example, 150 offices instead of 25 offices).

    Project Consultant Office Payroll Travel Admin Total
    10001 Consultant 1 Office 1 15201 1500 250 16951
    10004 Consultant 4 Office 1 15207 1503 253 16963
    10008 Consultant 8 Office 1 15215 1507 257 16979
    10012 Consultant 12 Office 1 15223 1511 261 16995
    10017 Consultant 17 Office 1 15233 1516 266 17015
    10022 Consultant 22 Office 1 15243 1521 271 17035
    10026 Consultant 26 Office 1 15251 1525 275 17051
    10030 Consultant 30 Office 1 15259 1529 279 17067
    10034 Consultant 34 Office 1 15267 1533 283 17083
    10038 Consultant 38 Office 1 15275 1537 287 17099
    10042 Consultant 42 Office 1 15283 1541 291 17115
    10046 Consultant 46 Office 1 15291 1545 295 17131
    10050 Consultant 50 Office 1 15299 1549 299 17147
    10054 Consultant 54 Office 1 15307 1553 303 17163
    10058 Consultant 58 Office 1 15315 1557 307 17179
    10062 Consultant 62 Office 1 15323 1561 311 17195
    10065 Consultant 65 Office 1 15329 1564 314 17207
    10069 Consultant 69 Office 1 15337 1568 318 17223
    10073 Consultant 73 Office 1 15345 1572 322 17239
    10077 Consultant 77 Office 1 15353 1576 326 17255
    10081 Consultant 81 Office 1 15361 1580 330 17271
    10085 Consultant 85 Office 1 15369 1584 334 17287
    10089 Consultant 89 Office 1 15377 1588 338 17303
    10093 Consultant 93 Office 1 15385 1592 342 17319
    10097 Consultant 97 Office 1 15393 1596 346 17335


    382441 38708 7458 428607

    Question #3
    DYNAMIC AND CONNECTED, OFFSET ???
    Same chart as above- Output Sheet first table refers to the project tab and uses index match to pull the information with the use of a helper column. Sometimes it pulls many items (25) and sometimes it pull fewer (5). Is there any way to make use of the offset function or some other dynamic function to reduce the number or rows and columns when the number of items needing to be drawn changes? I am also interested in connecting ALL THREE OF THESE CHARTS (MASTER LIST BY OFFICE, OUTGOING FUNDS, AND INCOMING FUNDS) so that they are adjacent, dynamic, connected, and running off the same formula. That is to say that when the MASTER LIST BY OFFICE ends, it would sum itself and then the formula would automatically begin the OUTGOING FUNDS table, and after that one is finished it would automatically begin the INCOMING FUNDS table. They don't necessarily have to be all in the same formula, as long as I can find a way to tell them to begin and end closer or further from eachother as the entries change, that would be great. I am interested in having it constantly set up as though it were a report (without manually deletions, as they might forget to add it back as they search for a new office). I am not sure if this is possible.



    AGAIN, SORRY FOR THE LONG POST AND QUESTIONS. Hopefully, if this is resolved I may have some follow up questions to add to the same post if you experts don't mind answering them too. They would be mostly slight deviations of this question though.
    Thanks so much,
    Underling


    P.S
    MY EXAMPLE FILE
    Index Match If Rows Max Count If Lookup CONFUSED_macro 3.xlsm
    Last edited by Underling; 10-30-2013 at 01:17 AM.

  2. #2
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Index, match, dynamic and connected without sorting helper column non-adjacent index m

    First off(from title) why no helper columns? (they can be hidden in final workbook, and generally make the calculations significantly faster)
    A picture may be worth a thousand words, BUT, a sample Workbook is worth a thousand screenshots!
    -Add a File - click advanced (next to quick post), scroll to manage attachments, click, select add files, click select files, select file, click upload, when file shows up at bottom left, click done (bottom right), click submit
    -To mark thread Solved- go top of thread,click Thread Tools,click Mark as Solved
    If you received helpful response, please remember to hit the * of that post

  3. #3
    Registered User
    Join Date
    10-23-2013
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Index, match, dynamic and connected without sorting helper column non-adjacent index m

    Well, I have helper columns in here but I don't want them if they are working the way they are currently working now. Right now the helper columns have to be sorted adjacently and that's not going to be convenient for the users. I'm not altogether opposed to helper columns if they can work while non-adjacent, but because of the understanding level of the users, the simpler the formulas can be (less helper columns and references) the better.

  4. #4
    Registered User
    Join Date
    10-23-2013
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Index, match, dynamic and connected without sorting helper column non-adjacent index m

    Anyone have any idea or have i made it too confusing?

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

    Re: Index, match, dynamic and connected without sorting helper column non-adjacent index m

    Forum rules suggest waiting about 24 hrs before bumping

  6. #6
    Registered User
    Join Date
    10-23-2013
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Index, match, dynamic and connected without sorting helper column non-adjacent index m

    I was very close to 24 hours, sorry for not f0ollowing the suggestion. Does anyone know the solution to my question, or was my spreadsheet too big? =0

  7. #7
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Index, match, dynamic and connected without sorting helper column non-adjacent index m

    Well, I have 3 separate solutions to the 2nd Question (mainly because it's the first in your workbook )
    See attached, I would post the formulas, but that means transferring ranges as I post, and I may miss something !

    First solution is a single cell solution (no helper columns/cells required)
    Second Solution uses the helper column ( sort of, it actually uses the MAX(range) cell on output sheet) to speed calculations a bit
    Third solution uses the built in table abilities of Excel to give the desired result (Using Master List, (copied to Sheet1), Subtotals and Filter on Office #)

    I also moved the Helper column to column H (and out of the table) on Master List, and made the reference to office # on Master List (J6) = Output Sheet (C1), to insure the values being looked at are the same
    Also added another Office 2 entry at the end of the list, so that you can see my formulas work for unsorted data (to check, for first 2 solutions, Change C1 to Office 2, You should get 2 Entries for office 2 (Instead of 1, the formula you have will give that, but if you add another Office 1 Entry, you get some "weird results"))
    Also, I left a row between the List, and the Sub-Totals for the first 2 solutions
    A Note, You can carry first 2 solutions down as far as you need, BUT if you overwrite them with the other 2 questions answers, you may lose data, so decide the maximum lenght for your information, then keep outside those bounds for the other answers..you may have to use VBA to hide the blank rows, for printing purposes, or display purposes, but try not to overwrite the formulas that already exist, just because you have a certain image in mind that does not fit what your data is !

    As for the Q1 and Q3 parts of your OP, I'm not really sure what YOU expect to see there, so I left that alone till you can give more info on those
    (Especially not certain on why you need a custom Eval Function, Index's and Matches (or other ways of getting row/column numbers should work)) (Not that having your own Eval Function is bad,just wondering why you feel it needed here)?

    Hope this helps

+ 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. Index and Match Only If Adjacent Cell Value is True
    By Duggerz in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-19-2013, 07:29 PM
  2. Index match offset to get subsequent index values in a column
    By Andrew_Step in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-06-2013, 02:55 PM
  3. Replies: 3
    Last Post: 05-02-2013, 01:31 AM
  4. [SOLVED] VBA for creating Index-Match every 2 rows where INDEX refer to a different column per row
    By bisaya789 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-22-2012, 03:46 AM
  5. match value in one column to adjacent value: use vlookup or index/match?
    By conorsgaffney in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-28-2010, 04: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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1