Microsoft Office Forums Do I need a power query for this one?

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 04-09-2019, 03:32 AM
shabbaranks shabbaranks is offline Do I need a power query for this one? Windows 7 64bit Do I need a power query for this one? Office 2007
Advanced Beginner
Do I need a power query for this one?
 
Join Date: Mar 2011
Posts: 87
shabbaranks is on a distinguished road
Default Do I need a power query for this one?

i,


Im familiar with SQL queries and so Im thinking I should be able to achieve a query within Excel - am I right ????

What I would like to do is - I have a sheet which imports data from an external source and data extracted from another source - both of which could contain multiple invoice numbers. I would like to be able to show those complete orders on a 3rd sheet based on the order number within the cell. I have attached an example which shows DataA (which would be the SQL data) DataB another source of data and then combined data which has the invoice number atthe top and then would pull through from the other 2 worksheets the data.
Thanks
Attached Files
File Type: xlsx Example.xlsx (10.8 KB, 6 views)
Reply With Quote
  #2  
Old 04-09-2019, 08:13 AM
xor xor is offline Do I need a power query for this one? Windows 10 Do I need a power query for this one? Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,028
xor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of light
Default Do I need a power query for this one?

Is it like shown in the attached?
Attached Files
File Type: xlsx InvoiceNumber.xlsx (18.3 KB, 2 views)
Reply With Quote
  #3  
Old 04-10-2019, 12:48 AM
shabbaranks shabbaranks is offline Do I need a power query for this one? Windows 7 64bit Do I need a power query for this one? Office 2007
Advanced Beginner
Do I need a power query for this one?
 
Join Date: Mar 2011
Posts: 87
shabbaranks is on a distinguished road
Default

Thanks Xor I went about it a different way but am not quite there - it didn't help that my example didn't have the correct\matching columns for the 2 data extracts so I apologise about that.

I think I can get away with an INDEX & MATCH which as on my new attached example is working for the data which is manually entered into Data2 but doesn't work for the data which is a link to an SQL table\stored procedure.

Thanks
Attached Files
File Type: xlsx Example.xlsx (10.6 KB, 1 views)
Reply With Quote
  #4  
Old 04-10-2019, 02:03 AM
xor xor is offline Do I need a power query for this one? Windows 10 Do I need a power query for this one? Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,028
xor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of light
Default

Does that mean that you don't need any further help?
Reply With Quote
  #5  
Old 04-10-2019, 02:57 AM
shabbaranks shabbaranks is offline Do I need a power query for this one? Windows 7 64bit Do I need a power query for this one? Office 2007
Advanced Beginner
Do I need a power query for this one?
 
Join Date: Mar 2011
Posts: 87
shabbaranks is on a distinguished road
Default

Sorry - I would like help if that's ok? As my Index and Match doesn't work for the data which is linked to the sheet via a data connection to an SQL server but does work for data that's typed into the 2nd spread sheet and I cant work out the reason?
Reply With Quote
  #6  
Old 04-10-2019, 03:29 AM
xor xor is offline Do I need a power query for this one? Windows 10 Do I need a power query for this one? Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,028
xor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of light
Default

I tried to solve what you asked for in your original question. In your first Example file you had more instances of the same Invoice Number (126799). That's why I posted the solution I did. You can't solve that with the INDEX/MATCH formula you have shown in #3.

If it is a problem with your SQL-query I don't think I can help you.

The reason that your formulas returns #N/A is that the "numbers" i column A in SQLSheet and DataSheet are text while the number in Combined!H1 is a real number.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Issues Using Power Query file in Excel 2016 and then in 2013 bld9936 Excel Programming 0 02-27-2017 12:50 PM
Query and Power Query not working Excel 2016 bl10 Excel 0 07-22-2016 06:25 PM
can power point LINK to embedded object in power point ? johnseito PowerPoint 0 05-24-2014 04:31 AM
How to Query Text in MS-Excel 2010 or Import Data & Query outside of Excel? mag Excel 0 10-18-2012 11:15 AM
Microsoft Power Point 2004 to Office Power Point 2007 chuff PowerPoint 0 03-20-2011 01:23 PM


All times are GMT -7. The time now is 09:33 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2019, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft