May 29 2019 04:40 AM
How would I go about transferring data from numerous sheets, and condensing it on another sheet depending on whether (on the original sheets) the data has a certain text in a specific column.
Here's an example (See attached). So here I have Sheet1, Sheet2 and Sheet3 and in the column titled 'Suppliers' I have two different sup - Jewsons and Harveys. Now, I want the data from the first three sheets to populate the 'Jewsons' sheetpliers IF the data has 'Jewsons' in its suppliers column. So for example,
In Sheet1 Jewsons supply 12 'timber' at location S in week 1 - So this data should go across to the Jewsons tab. However, the paper in Sheet1 is being supplied by Harveys and therefore should not be shown in the Jewsons tab.
EXTRA: In Sheet1 and Sheet2 timber is being supplied by Jewsons, and its in the same lovcation and week, how would i then go about adding the quantity in the Jewsons tab.
Many thanks!
Bailey100
May 29 2019 05:17 AM
SolutionHi,
This is can be done using Get & Transform (aka Power Query).
Please check out the solution in the attached file.
Hope that helps
May 29 2019 06:32 AM
@Haytham Amairah Thankyou! But I don't have this add-on and therefore power query isn't available to me. Is there any other way of solving my problem without the add-on.
May 29 2019 07:02 AM
Hi @Bailey100,
If you have Excel 2016 or later, you don't need the add-in because it's now built in Excel as (Get and Transform Data) as the below screenshot and you can find it in the Data tab.
If you have an earlier version of Excel, you can download the Power Query Add-in from here.
Without the Power Query, the solution could be difficult.
May 30 2019 01:21 AM
Hi, @Haytham Amairah
Is there any step by step tutorial online that you used for the power query as I have just downloaded the add-in and don't know where to start!
Many Thanks
Bailey100
May 30 2019 06:30 AM - edited May 30 2019 06:31 AM
Hi @Bailey100,
I've created a quick video to show you how I've accomplished this task.
Please check it out here.
There are many tutorials online can help you to to learn Power Query, and this is one of them:
Getting Started with Power Query
Hope that helps
May 30 2019 08:14 AM
@Haytham Amairah Thanks very much!
May 29 2019 05:17 AM
SolutionHi,
This is can be done using Get & Transform (aka Power Query).
Please check out the solution in the attached file.
Hope that helps