Hi I need help to transform sheet 1 data to sheet 2 format. Does anyone know of a formula that i can key into cell B2 of sheet 2? Preferably a single formula that I can key into cell B2 of sheet 2 then drag it across to D5!
Was thinking of combining vlookup, match and countifs but wasn't sure how should I present them as an equation.
Thanks!
Last edited by johnn123; 07-14-2022 at 04:39 AM.
Administrative Note:
Members will tailor the solutions they offer to the version (NOT a release number like 2206) of Office (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this. Thanks.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Put this in B2 on Sheet2 then copy right and down as required.
Formula:
Please Login or Register to view this content.
Note, all instances of "Watermelon" on Sheet1 have trailing spaces so you'll need to tidy those up to get the correct figures in column D on Sheet2.
BSB
You can use UI clicks in PowerQuery (Get & Transform).
M Code:
Please Login or Register to view this content.
Worksheet or Tab name : Sheet2
Cell B2 formula , Drag down and across
Formula:Please Login or Register to view this content.
Hey. Your MS Office version is a bit odd but it's the same build as mine I think so I'm assuming you're on O365? If so, the following in A1 of the second sheet might work for you:
This formula generates the whole array with the first column and first row filled in - no other copying across or down required.Please Login or Register to view this content.
WBD
Office 365 on Windows 11, looking for ✶ rep!
@WBD
The Office version has now been clarified - it's MS365.
Yeah. I guessed as much. The OP was actually using information from their "About Excel" which, on mine, says "Version 2206 (Build 15330.20230 Click-to-Run)" so it's a reasonable mistake to make. Anyway, it's the first time I've used MAKEARRAY with a LAMBDA function so I was very excited. I live a sad life ...
WBD
It worked! thanks!
Thank you! This worked!
@johnn123 You're Welcome. Glad to help . Thank You for the feedback and rep.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks