SOLVED
Home

Extract specific data from numerous sheets to populate one.

%3CLINGO-SUB%20id%3D%22lingo-sub-655036%22%20slang%3D%22en-US%22%3EExtract%20specific%20data%20from%20numerous%20sheets%20to%20populate%20one.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-655036%22%20slang%3D%22en-US%22%3E%3CP%3EHow%20would%20I%20go%20about%20transferring%20data%20from%20numerous%20sheets%2C%20and%20condensing%20it%20on%20another%20sheet%20depending%20on%20whether%20(on%20the%20original%20sheets)%20the%20data%20has%20a%20certain%20text%20in%20a%20specific%20column.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere's%20an%20example%20(See%20attached).%20So%20here%20I%20have%20Sheet1%2C%20Sheet2%20and%20Sheet3%20and%20in%20the%20column%20titled%20'Suppliers'%20I%20have%20two%20different%20sup%26nbsp%3B-%20Jewsons%20and%20Harveys.%20Now%2C%20I%20want%20the%20data%20from%20the%20first%20three%20sheets%20to%20populate%20the%20'Jewsons'%20sheet%3CSPAN%3Epliers%20IF%20the%20data%20has%20'Jewsons'%20in%20its%20suppliers%20column.%20So%20for%20example%2C%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EIn%20Sheet1%20Jewsons%20supply%2012%20'timber'%20at%20location%20S%20in%20week%201%20-%20So%20this%20data%20should%20go%20across%20to%20the%20Jewsons%20tab.%20However%2C%20the%20paper%20in%20Sheet1%20is%20being%20supplied%20by%20Harveys%20and%20therefore%20should%20not%20be%20shown%20in%20the%20Jewsons%20tab.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEXTRA%3A%20In%20Sheet1%20and%20Sheet2%20timber%20is%20being%20supplied%20by%20Jewsons%2C%20and%20its%20in%20the%20same%20lovcation%20and%20week%2C%20how%20would%20i%20then%20go%20about%20adding%20the%20quantity%20in%20the%20Jewsons%20tab.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMany%20thanks!%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBailey100%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-655036%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20Online%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-655133%22%20slang%3D%22en-US%22%3ERe%3A%20Extract%20specific%20data%20from%20numerous%20sheets%20to%20populate%20one.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-655133%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F351100%22%20target%3D%22_blank%22%3E%40Bailey100%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20can%20be%20done%20using%20Get%20%26amp%3B%20Transform%20(aka%20Power%20Query).%3C%2FP%3E%3CP%3EPlease%20check%20out%20the%20solution%20in%20the%20attached%20file.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-655328%22%20slang%3D%22en-US%22%3ERe%3A%20Extract%20specific%20data%20from%20numerous%20sheets%20to%20populate%20one.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-655328%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F35679%22%20target%3D%22_blank%22%3E%40Haytham%20Amairah%3C%2FA%3E%26nbsp%3BThankyou!%20But%20I%20don't%20have%20this%20add-on%20and%20therefore%20power%20query%20isn't%20available%20to%20me.%20Is%20there%20any%20other%20way%20of%20solving%20my%20problem%20without%20the%20add-on.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-655387%22%20slang%3D%22en-US%22%3ERe%3A%20Extract%20specific%20data%20from%20numerous%20sheets%20to%20populate%20one.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-655387%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F351100%22%20target%3D%22_blank%22%3E%40Bailey100%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20have%20Excel%202016%20or%20later%2C%20you%20don't%20need%20the%20add-in%20because%20it's%20now%20built%20in%20Excel%20as%20(Get%20and%20Transform%20Data)%20as%20the%20below%20screenshot%20and%20you%20can%20find%20it%20in%20the%20%3CSTRONG%3EData%3C%2FSTRONG%3E%20tab.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F116472iBCC9F3A7425CCE40%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Get%20%26amp%3B%20Transform%20Data.png%22%20title%3D%22Get%20%26amp%3B%20Transform%20Data.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20have%20an%20earlier%20version%20of%20Excel%2C%20you%20can%20download%20the%20Power%20Query%20Add-in%20from%20%3CA%20href%3D%22https%3A%2F%2Fwww.microsoft.com%2Fen-us%2Fdownload%2Fdetails.aspx%3Fid%3D39379%26amp%3BCorrelationId%3D5adf6c76-579e-4bd4-9efe-58c3ddc8a774%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehere%3C%2FA%3E.%3C%2FP%3E%3CP%3EWithout%20the%20Power%20Query%2C%20the%20solution%20could%20be%20difficult.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-657576%22%20slang%3D%22en-US%22%3ERe%3A%20Extract%20specific%20data%20from%20numerous%20sheets%20to%20populate%20one.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-657576%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F35679%22%20target%3D%22_blank%22%3E%40Haytham%20Amairah%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20any%20step%20by%20step%20tutorial%20online%20that%20you%20used%20for%20the%20power%20query%20as%20I%20have%20just%20downloaded%20the%20add-in%20and%20don't%20know%20where%20to%20start!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMany%20Thanks%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBailey100%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-658108%22%20slang%3D%22en-US%22%3ERe%3A%20Extract%20specific%20data%20from%20numerous%20sheets%20to%20populate%20one.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-658108%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F351100%22%20target%3D%22_blank%22%3E%40Bailey100%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3EI've%20created%20a%20quick%20video%20to%20show%20you%20how%20I've%20accomplished%20this%20task.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EPlease%20check%20it%20out%20%3CA%20href%3D%22https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3DGo2hYYiizu8%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehere%3C%2FA%3E.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere%20are%20many%20tutorials%20online%20can%20help%20you%20to%20to%20learn%20Power%20Query%2C%20and%20this%20is%20one%20of%20them%3A%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2Fgetting-started-with-power-query-7104fbee-9e62-4cb9-a02e-5bfb1a6c536a%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3EGetting%20Started%20with%20Power%20Query%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-658468%22%20slang%3D%22en-US%22%3ERe%3A%20Extract%20specific%20data%20from%20numerous%20sheets%20to%20populate%20one.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-658468%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F35679%22%20target%3D%22_blank%22%3E%40Haytham%20Amairah%3C%2FA%3E%26nbsp%3BThanks%20very%20much!%3C%2FP%3E%3C%2FLINGO-BODY%3E
Bailey100
Contributor

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

6 Replies
Solution

Hi,

@Bailey100

 

This is can be done using Get & Transform (aka Power Query).

Please check out the solution in the attached file.

 

Hope that helps

 @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.

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.

 

Get & Transform Data.png

 

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.

 

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

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