Page 5 of 6 FirstFirst ... 3 4 5 6 LastLast
Results 41 to 50 of 51

Thread: Specification Comparison Table from Different Worksheets

  1. #41
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,695
    Articles
    0
    Excel Version
    Office 365 Subscription


    Register for a FREE account, and/
    or Log in to avoid these ads!

    OK - so I had a look. Guess what? It's EXACTLY what I said it might be back in post #30 that YOU said you'd checked, but clearly not!!!

    There are trailing spaces in the following cells on the COMPARE tab: B12, A37, A40, A42, A49 and A54. Get rid of them and the formula will work fine.
    Ali
    Enthusiastic self-taught user of MS Excel!

  2. #42
    Acolyte Polignac's Avatar
    Join Date
    Apr 2021
    Posts
    23
    Articles
    0
    Excel Version
    Office 365
    Hi Ali, yes I've been trialing different things before coming back to the forum, last i check i created 5 excel files without success. Thanks for the help!

  3. #43
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,695
    Articles
    0
    Excel Version
    Office 365 Subscription
    Obviously not what I'd told you to check - see post #41.
    Ali
    Enthusiastic self-taught user of MS Excel!

  4. #44
    Acolyte Polignac's Avatar
    Join Date
    Apr 2021
    Posts
    23
    Articles
    0
    Excel Version
    Office 365
    What are trailing spaces? I've tried to hit delete on all the cells affected?

  5. #45
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,695
    Articles
    0
    Excel Version
    Office 365 Subscription
    If you place your cursor into those cells, you will see that there is a space after the final letter. You will need to delete the space in each case, then the lookup formula will find a match.
    Ali
    Enthusiastic self-taught user of MS Excel!

  6. #46
    Acolyte Polignac's Avatar
    Join Date
    Apr 2021
    Posts
    23
    Articles
    0
    Excel Version
    Office 365
    Doh! I've been clicking at the wrong cells. It finally worked... just 1 last thing, values return for row 28-32 is not what is expected. I'm expecting results return of X, Y, Z, T, R. Instead i'm getting Z, T, R etc.

  7. #47
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,695
    Articles
    0
    Excel Version
    Office 365 Subscription
    In D28 copied down to D32:

    =IFERROR(INDEX(Composite[Value],AGGREGATE(15,6,ROW(Composite[Brand & Model])-1/((Composite[Brand & Model]=D$1)*(Composite[Parameters]=$A$28)),ROWS($1:1))),"")

    Now you can select cells D28 to D32 – CTRL+C to copy – select cells E28 to H32 – right-click and click on Paste Formulas (the third clipboard icon with fx on it).
    Ali
    Enthusiastic self-taught user of MS Excel!

  8. #48
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,695
    Articles
    0
    Excel Version
    Office 365 Subscription
    So are we there yet?
    Ali
    Enthusiastic self-taught user of MS Excel!

  9. #49
    Acolyte Polignac's Avatar
    Join Date
    Apr 2021
    Posts
    23
    Articles
    0
    Excel Version
    Office 365
    Hi Ali. Yes. I'm over the moon with the results. It's really fascinating seeing this coming to life. Thank you so much for your help in this. Really really appreciate your tremendous help in this.

    Thank you!

  10. #50
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,695
    Articles
    0
    Excel Version
    Office 365 Subscription
    Thanks for letting me know - there's nothing more frustrating than being left hanging, especially when one has made such a big effort.

    Have a long and happy life with PowerQuery!
    Ali
    Enthusiastic self-taught user of MS Excel!

Page 5 of 6 FirstFirst ... 3 4 5 6 LastLast

Posting Permissions

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