1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

To Lookup Batch No's from Multiple Tabs in Another File

Discussion in 'Ask an Excel Question' started by fareedexcel, Mar 9, 2018.

  1. fareedexcel

    fareedexcel Member

    Messages:
    61
    Hello,

    I have 2 sheets (File 1 - with Batch No and Model No of different Customers, File 2 - Model No's in different tabs)

    I need to fetch the Batch No. data from File 1 corresponding to the Model No's name in File 2.

    Please assist.

    Attached Files:

  2. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    751
    As I understand it, this might work:
    In A1 on all sheets of File 2
    =MID(CELL("filename",A1),FIND("]",CELL("filename",A1),1)+1,4)
    in C1 to G1 : "Customer 1" ... "Customer 5"
    In C2 (drag down and to the right):
    EDIT (no space required for other sheet names beside "Customer 1 " ). If you correct the sheetname to "Customer 1", then formula is generic like this.
    =IFERROR(AGGREGATE(15,6,(INDIRECT("'[Batch No''s - File 1.xlsx]"&C$1&"'!$B$2:$B$500")/(INDIRECT("'[Batch No''s - File 1.xlsx]"&C$1&"'!$d$2:$d$500")=$A$1)),ROWS($C$2:$C2)),"")

    It will give you the list of batch numbers of all the sheets "customer" for each of the model numbers. Like below
    upload_2018-3-9_17-43-11.png

    Both files need to be open though. If calculations need to work even when file 1 is open, then another solution must be found.

    Attached Files:

    Last edited: Mar 9, 2018
  3. fareedexcel

    fareedexcel Member

    Messages:
    61
    I wasn't able to download the attached answer file of Grah. Any issue?
  4. fareedexcel

    fareedexcel Member

    Messages:
    61
    Capture.PNG Thanks for the help. But i need the output in this way in File 2
  5. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    751
    Perhaps I was in the edit mode and the file was actually deleted by myself.
    But the basic concept is workable for you? In addition you need the full list?
    Basically the same "aggregate" formula can be used. Would this do?

    Attached Files:

    fareedexcel likes this.
  6. fareedexcel

    fareedexcel Member

    Messages:
    61
    Thanks GraH, In the Column C, Customer 1 results are not fetching from File 1. Apart from that data is capturing perfectly. Only in Column 1 data is not getting fetched.
  7. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    751
    Did you rename your sheet as suggested to "Customer 1" without the extra space?
    fareedexcel likes this.
  8. fareedexcel

    fareedexcel Member

    Messages:
    61
    Thanks GraH, Now the data is capturing perfectly. As you mentioned there is an additional space in the Tab 1 of "Customer 1 ".
  9. fareedexcel

    fareedexcel Member

    Messages:
    61
    When I used the same above code in my original sheet, no data is populating in my File 2 sheet. I have no clue why the data is not fetching from the original sheet. Please assist

    File Name = AmjadOrder

  10. fareedexcel

    fareedexcel Member

    Messages:
    61
    Now I bit changed the formula by including " ' ". Still I'm getting #NUM! Error

  11. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    751
    hi,
    At first glance, nothing seems wrong with the syntax
    Code (vb):
    =AGGREGATE(15,6,(INDIRECT("'[AmjadOrder.xlsm]"&D$1&"'!$A$2:$A$500")/(INDIRECT("'[AmjadOrder.xlsm]"&D$1&"'!$B$2:$B$500")=$A$1)),ROWS($D$2:$D2))
    Try the formula audit function. It might help you to detect where the error comes from. If you don't know how, then care to look at this post https://chandoo.org/forum/threads/if-formula-required.37320/#post-224310. I explained message #10 how to use formula audit.
  12. fareedexcel

    fareedexcel Member

    Messages:
    61
    In my Original File No.1, Batch No is alphanumeric whereas in my example sheet it is only numbers may be that's the reason I'm getting error.

    In this case please assist how I can rectify the error. My batch no is as follows,

    ST0004
    ST0005
    ST0006
  13. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    751
    Indeed the formula only works for numbers. That is why it is always better to upload a file with representative data.
    Back to start, without cashing in...
  14. fareedexcel

    fareedexcel Member

    Messages:
    61
    Just now realized when I'm trying to figure out why the formula doesn't supporting. So we cannot alter the formula with text function?. I have wasted your time too.
  15. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    751
    Hi, the first formula needs a tweak like this
    Code (vb):
    =IFERROR(INDEX(INDIRECT("'[Batch No''s - File 1.xlsx]"&C$1&"'!$B$1:$B$500"),AGGREGATE(15,6,(ROW(INDIRECT("'[Batch No''s - File 1.xlsx]"&C$1&"'!$B$1:$B$500"))/((INDIRECT("'[Batch No''s - File 1.xlsx]"&C$1&"'!$D$1:$d$500"))=$A$1)),ROWS(C$2:C2)),1),"")
    fareedexcel likes this.
  16. fareedexcel

    fareedexcel Member

    Messages:
    61
    Hi Grah,

    I used the above formula and it is capturing the data now. when it comes to capture the complete batch list I used the same formula which you have defined. But the output seems blank


  17. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    751
    Hi Fareed, my original formula cannot work because of the same "those aren't numbers". I assumed you'd get that. I did not post a solution for the second formula, since I could not get it to navigate to the next column in the array correctly. But I know it is possible.
  18. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    1,851
    Hi,

    I combine all your worksheets into one workbook and convert all numeric value into alphanumeric value.

    1] Select A1 dropdown list for criteria

    2] In "full list" B2, copied down :

    =IFERROR(INDEX(C$2:G$10,MOD(ROWS($B$2:$B2)-1,COUNTIF(C$2:C$10,"?*"))+1,INT((ROWS($B$2:$B2)-1)/COUNTIF(C$2:C$10,"?*")+1)),"")

    Regards
    Bosco

    Attached Files:

    Last edited: Mar 12, 2018
  19. fareedexcel

    fareedexcel Member

    Messages:
    61
    Thanks Bosco. I have tried with the formula and it is working fine. I applied the same formula in the Google Spreadsheet. It's giving me an error #Name?. When checked it's saying aggregate is an unknown function.?
  20. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    1,851
    1] Try to use SMALL+IF instead of AGGREGATE, as per attached file.

    2] In the attachment "Customer list" C2, array formula copied across to G2 and all copied down :

    =IFERROR(INDEX(INDIRECT("'"&C$1&"'!$B$1:$B$500"),SMALL(IF(INDIRECT("'"&C$1&"'!$D$1:$D$500")=$A$1,ROW(INDIRECT("'"&C$1&"'!$B$1:$B$500"))),ROWS($C$2:$C2))),"")

    p.s.
    array formula to be confirmed by pressing SHIFT+CTRL+ENTER 3 keystrokes together instead of just press ENTER

    Regards
    Bosco

    Attached Files:

    fareedexcel likes this.
  21. fareedexcel

    fareedexcel Member

    Messages:
    61
    Hi Bosco,

    I used the formula in the google spreadsheet,

    =ArrayFormula(INDEX(INDIRECT("'"&D$2&"'!$C$5:$C$500"),SMALL(IF(INDIRECT("'"&D$2&"'!$H$5:$H$500")=$A$2,ROW(INDIRECT("'"&D$2&"'!$C$5:$C$500"))),ROWS($C$3:$C3))))

    i'm getting an error saying small function has no input value data.

    Regards,
    Fareed

    Attached Files:

  22. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    1,851
    I am not familiar with Google Spreadsheet formula,

    but in view of Excel formula, your formula made error and I corrected in red as below :

    =ArrayFormula(INDEX(INDIRECT("'"&D$2&"'!$C$5:$C$500"),SMALL(IF(INDIRECT("'"&D$2&"'!$H$5:$H$500")=$A$2,ROW(INDIRECT("'"&D$2&"'!$C$1:$C$496"))),ROWS($C$3:$C3))))

    Should you still got an error, please redirect your question to the Google Sheet forum to solve.

    Regards
    Bosco
    Last edited: Mar 25, 2018
    fareedexcel likes this.
  23. fareedexcel

    fareedexcel Member

    Messages:
    61
    Hi Bosco,

    Thanks for the change. Code is working and the data is getting captured as per the requirement. There is some problem when the full list is getting captured.

    1. In the column B, full list of the names to be captured. But if there is any space or gaps, it is missing out the data to capture.
    2. As per the attached image, column E has too many entries but in the column B the data is not capturing fully.

    Requirement is to capture data from all the columns from C3 to AB300. if there is no entries then it should move to the next column to capture the data.

    The code used is

    Code (vb):
    =IFERROR(INDEX(C$3:AB$300,MOD(ROWS($B$3:$B3)-1,COUNTIF(C$3:C$300,"?*"))+1,INT((ROWS($B$3:$B3)-1)/COUNTIF(C$3:C$300,"?*")+1)),"")

    Attached Files:

  24. fareedexcel

    fareedexcel Member

    Messages:
    61
    Hi Bosco & Experts,

    Could you please provide solution for my above query if possible.
  25. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    1,851
    Hi,

    1] The above mentioned formula only work for a source rectangular range without blank cells as per your post #.4 screenshot.

    2] Your new requirement in post #.23 show the source rectangular range with blank cells of which can work in an expensive formula, but provided that the source data cell amount in around few hundreds only.

    3] Here is an example file attached file show how the formula work for reference only :

    In "Sheet 1" B3, formula copied down :

    =IFERROR(INDIRECT(TEXT(RIGHT(AGGREGATE(15,6,ROW($3:$13)/1%+COLUMN(C:J)*100001/(C$3:J$13<>""),ROW(A1)),4),"R0C00"),),"")

    4] However, as per your mentioned that your data were in C3:AB300, that is 15,600 cells. I am afraid the above formula unable to suit with your requirement and advice you to seek for help in VBA to solve.

    Regards
    Bosco

    Attached Files:

    fareedexcel likes this.

Share This Page