Noob question - Excel queries

Excel_Blonde

New Member
Joined
Aug 8, 2018
Messages
44
Hi all,

I'm very new to excel queries and was wondering if i'd better off using VBA for what I'm trying to achieve?

Also are queries the same as power query? Is power query an add in? (Like I say I'm very new)

Essentially, I'm trying to combine worksheets into one Master workbook and then use conditions to return a list into another worksheet within the same Master workbook.

So far I have managed to create a query which imports all data sheets within a specified folder. I've managed to set it to automatically refresh upon loading (including any new worksheets).

Now what I need to do is to drill down further, hopefully the below table will help to explain.

If the unique identifier2 appears 3 times with different unique identifier1's and Names I would it to list into sheet2. List all entries meeting this criteria.

NameUnique Identifier 1Unique identifier 2Unique identifier 3P1P2A1A2
Order1Op1UnpeelBanana2507
Order1Op2TestBanana2735
Order2Op1UnpeelBanana2505
Order2Op2TestBanana2754
Order3Op1WashApple2556
Order3Op2TestApple4564
Order4Op1UnpeelBanana5648
Order4Op2SliceBanana4638
Order4Op3TestBanana4586
Order5Op1WashOrange4859
Order5Op2TestOrange1245
Order5Op3UnpeelOrange5849

<tbody>
</tbody>

Note. The number of ops can change for the same unique identifier, e.g one order for banana can have 2 ops but another can have 3 ops.

I hope this makes sense.
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Edit: If the unique identifier3 appears 3 times with different unique identifier1's and Names I would it to list into sheet2. List all entries meeting this criteria.
 
Upvote 0
it is not easy to use formulas in pq. why not add another column with excel formula? if you want to count how many in one column, you will have to create new table/list with unique values in pq.
IF(COUNTIF(ID3+Names,[ID3+Names])=3,"Y","N") (ID3+Names)(New column)=Concat(ID3,Names) )

query is data filter, perhaps that is the best way to put it. so your query is in fact a piece of code. return(colA, colB, colC).where(value colA=3).format(colA=Number,colB=Text,colC=Text)
A B C D E
1 a b g 2
4 f g f 2
3 f f g 2

returns 3 f f

pq used to be add on, now it is part of excel
 
Upvote 0
I don't really understand your data. However to avoid adding the helper column in the Excel table how about duplicating your query in PQ then grouping by your conditions columns and returning the count value. Filter count by greater than 3. Then join back to the original query .
Peter
 
Upvote 0
I don't really understand your data. However to avoid adding the helper column in the Excel table how about duplicating your query in PQ then grouping by your conditions columns and returning the count value. Filter count by greater than 3. Then join back to the original query .
Peter

Thank you both for your responses.

The data essentially is a list of job bookings. The name is the unique job name, unique identifier 1 is the operation number, unique identifier 2 is the Operation name, unique identifier 3 is the name of the item we are making (part code). The values are planned run and set times & actual run and set times. I want to investigate any item that has ran more than 3 times. With the example data I would like to return all data referring to banana as it has ran 3 times (it has 3 order numbers).

I will try to concatenate the job name and unique identifier 3 to see if I can work around that way, my knowledge of queries is still very green.
 
Upvote 0
This sounds like a simple job for Power Query, but can you post a few of the workbooks so we can see what we are working with? Also, your code that merges the data might help.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,213
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top