Hi,
I’ve posted this a few weeks ago & it was suggested doing it in Access. I had a go but not successful, I feel a little more comfortable and have a little more understanding in Excel.
Please see attached example - I would like a result sheet with the Region/Outstanding units that have not been inspected,
Cheers
You can do this using Power Query (Get & Transform Data):
Format your Issued Units data as a table ("tblIssued") and your Returned Inspected Units data as a table ("tblReturned"), then use the following query:
Please Login or Register to view this content.
let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source
If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE
Walking the tightrope between genius and eejit...
@Arch
I closed your previous thread with the same subject to avoid confusion. is that OK with you?
Thank you.
Hi,
Thank you for your prompt reply. Power query is something I've never come across. I've managed to get into the editor and vaguely understand how it's working. The Regions will increase as the work goes on so I’ve had gone into the Advance editor and added , “Data.5” is this correct ? it will end up with around 350 Regions - will this be OK ?
Cheers
Here's an edited version, which will cope with dynamic numbers of rows, for each region. It will handle any number of regions.
I'm not convinced your output format is the most usable, especially with 350 columns - but that's another question!Please Login or Register to view this content.
Cheers that works a treat - The output format is ok; I can see why you are saying what you’re saying. Is it possible to still show the region as a header even when the result is Zero and show 0 as a result? I hadn’t thought about this I need the status of every region,
Thank you
Easy way, but this returns null, rather than zero, in the totals row:
Slightly longer winded, but returns zero in totals:Please Login or Register to view this content.
Another thought crosses my mind - you realise you can connect the two tables directly to Access, you don't have to paste into Excel, first. Just replace these lines:Please Login or Register to view this content.
with something like:Please Login or Register to view this content.
Please Login or Register to view this content.
Last edited by Olly; 05-15-2019 at 11:03 AM.
Morning Olly,
Once again thank you for your support and yes the link to Access will be a plus. Can I link to two queries I have set up in Access? the queries hive off all the data I require from a certain date,
Cheers
Yes, you can get data from Access views (queries) or tables.
Hi Olly,
Please see attached Worksheet with a large amount of data. Unfortunately, it seems not to be doing the job across the whole of the Data -
MW23602 is correct 4 Issued 4 Returned zer0 result then MW11803 1 Issued 1 Returned 1 Outstanding. I’m wondering if it’s something I,ve done? Or not done,
Cheers
You have a data type mismatch between your two tables. Issued Units are stored as text in tblIssued, and as Variants in tblReturned, which means that numeric unit numbers aren't matching between the two tables.
Explicitly define the data type, in your query, by changing:
toPlease Login or Register to view this content.
Please Login or Register to view this content.
That works a treat - Cheers
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. You may also 'Add Reputation' to those who helped you. Thanks.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks