Consulting

Results 1 to 18 of 18

Thread: Formula to match information from a source sheet

  1. #1
    VBAX Regular
    Joined
    Mar 2023
    Posts
    13
    Location

    Formula to match information from a source sheet

    Hi, everyone,


    I attached a file, in hope that you will understand my need. I tried using Vlookup, but as you know, vlookup only finds first value. And my Excel knowledge is very limited.

    Basically, I need a formula to take the information from Database sheet (source) and place them in the CAR sheet, matching the date and the CAR name. I need this because i have a bigger number of cars to which i need to create a Daily activity sheet for vehicles.And if I could automatically import the information it would save me tons on time.

    Hope you will take some time to check the attachement. Many Thanks !
    Attached Files Attached Files

  2. #2
    is this close?
    Attached Files Attached Files

  3. #3
    Moderator VBAX Master georgiboy's Avatar
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    1,158
    Location
    One single formula in Excel 365
    Attached Files Attached Files
    Click here for a guide on how to add code tags
    Click here for a guide on how to mark a thread as solved

    Excel 365, Version 2401, Build 17231.20084

  4. #4
    VBAX Regular
    Joined
    Mar 2023
    Posts
    13
    Location
    Thanks for your responses!
    I attached another workbook, because i didnt add all the information.
    So... in database sheet i want to add all the cars with information from all the year long. There will be several sheets corresponding to each car (car1, car2 and so on). Each car will have 12 tables, from january to december.
    Each table in every car sheet should take it's information from database sheet.
    Basically: Car1 has 12 tables in it, from january from december (in the attached example i only have october and november). I want the formula to search in the database sheet information matching with car1 and it's corresponding month. Including the fuel info from the bottom on table (which can be optional).
    I really tried modifing your formulas, but i couldnt figuring out anything. My knowledge is tending to 0.

    Thank you for your time!
    Attached Files Attached Files

  5. #5
    Moderator VBAX Master georgiboy's Avatar
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    1,158
    Location
    Maybe like on the attached?
    Attached Files Attached Files
    Last edited by georgiboy; 03-28-2023 at 06:50 AM. Reason: Updated attachment to do bit at the bottom
    Click here for a guide on how to add code tags
    Click here for a guide on how to mark a thread as solved

    Excel 365, Version 2401, Build 17231.20084

  6. #6
    VBAX Regular
    Joined
    Mar 2023
    Posts
    13
    Location
    I tested it, added some more data and seems to be working great. I have a #SPILL! error on CAR1, can't figure out what the problem is. I deleted the duplicate values, the error persists. But CAR2 has no problems so far. You are the man!
    Any thoughts on Fuel area (i greened it out, on the bottom of the table) ?
    Many thanks !

  7. #7
    Moderator VBAX Master georgiboy's Avatar
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    1,158
    Location
    I updated my attachment to include the fuel part, I will have to investigate the spill error tomorrow although they are usually down to the formula not having enough room to spill into.
    Click here for a guide on how to add code tags
    Click here for a guide on how to mark a thread as solved

    Excel 365, Version 2401, Build 17231.20084

  8. #8
    VBAX Regular
    Joined
    Mar 2023
    Posts
    13
    Location
    Quote Originally Posted by georgiboy View Post
    I updated my attachment to include the fuel part, I will have to investigate the spill error tomorrow although they are usually down to the formula not having enough room to spill into.

    Only if you have the time. I myself checked the error, there is the same number on cells on each sheet. But as i mentioned, my Excel skill is veeeery slim.
    You were of great help! Thank you very much !

  9. #9
    Moderator VBAX Master georgiboy's Avatar
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    1,158
    Location
    It is because you have data in G2:J80, if you clear those cells the formula will spill. If any cells have data in where the formula is to spill you will get a spill error.
    Click here for a guide on how to add code tags
    Click here for a guide on how to mark a thread as solved

    Excel 365, Version 2401, Build 17231.20084

  10. #10
    Moderator VBAX Master georgiboy's Avatar
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    1,158
    Location
    I have updated the file i had with some XLOOKUP instead of filter for the main data. Fuel bit included.
    Attached Files Attached Files
    Click here for a guide on how to add code tags
    Click here for a guide on how to mark a thread as solved

    Excel 365, Version 2401, Build 17231.20084

  11. #11
    VBAX Regular
    Joined
    Mar 2023
    Posts
    13
    Location
    Thank you very much! It is what i needed. You are awesome!

    Edit:

    I added all the months for one car (b102pmh is the number plate) to fully-test it aaaand and i can't figure out the #SPILL! on the fuel are on january. All the other months are working, but January won't. I tried using the formula from the earlier methods, but still the error persists.
    Attached Files Attached Files
    Last edited by Aussiebear; 03-31-2023 at 02:09 PM. Reason: Removed the unnecessary quotation

  12. #12
    Moderator VBAX Master georgiboy's Avatar
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    1,158
    Location
    You can edit that formula to the below, you could also edit the other fuel calculations in the same way.
    =IFERROR(FILTER(Database!$K$2:$M$7000,(Database!$A$2:$A$7000=E2)*(MONTH(Database!$K$2:$K$7000)=MONTH(E1))*(Database!$K$2:$K$7000>0)),"")
    Click here for a guide on how to add code tags
    Click here for a guide on how to mark a thread as solved

    Excel 365, Version 2401, Build 17231.20084

  13. #13
    VBAX Regular
    Joined
    Mar 2023
    Posts
    13
    Location
    That did the trick !
    Thank you, I wish you all the best !
    Last edited by Aussiebear; 03-31-2023 at 02:08 PM. Reason: Removed the unnecessary quotation

  14. #14
    VBAX Regular
    Joined
    Mar 2023
    Posts
    13
    Location
    One more quick and maybe dumb question: is this working only on certain versions on Office (particulary 365?) ? Tried it on Office home and business 2019 and i got the #NAME? error. I didnt change anything on the workbook.
    The formulas changed like this
    =_xlfn.LET(_xlpm.f,_xlfn.XLOOKUP(A373&B373,Database!$A$2:$A$7000&Database!$B$2:$B$7000,Database!$C$2:$J$7000,"",0),_xlfn.MAP(_xlpm.f,_xlfn.LAMBDA(_xlpm.x,IF(_xlpm.x=0,"",_xlpm.x))))
    Last edited by Aussiebear; 04-02-2023 at 01:52 AM. Reason: Removed the unnecessary quotation

  15. #15
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,997
    Location
    Office 365 for Lambda. Will not work in prior versions.
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  16. #16
    which means back to old index/match for compatibility.

  17. #17
    VBAX Regular
    Joined
    Mar 2023
    Posts
    13
    Location
    Anyone willing to adapt it for the non 365 versions of office?
    Attached Files Attached Files

  18. #18
    i think Filter() is easy to do, you just have to make a little effort since GB already gave you some worksheet with example in it.
    try to learn from the example given and google those you are not familiar with.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •