Noob question - Excel queries

[Excel_Blonde]

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.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Unique Identifier 1[/TD]
[TD]Unique identifier 2[/TD]
[TD]Unique identifier 3[/TD]
[TD]P1[/TD]
[TD]P2[/TD]
[TD]A1[/TD]
[TD]A2[/TD]
[/TR]
[TR]
[TD]Order1[/TD]
[TD]Op1[/TD]
[TD]Unpeel[/TD]
[TD]Banana[/TD]
[TD]2[/TD]
[TD]5[/TD]
[TD]0[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]Order1[/TD]
[TD]Op2[/TD]
[TD]Test[/TD]
[TD]Banana[/TD]
[TD]2[/TD]
[TD]7[/TD]
[TD]3[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Order2[/TD]
[TD]Op1[/TD]
[TD]Unpeel[/TD]
[TD]Banana[/TD]
[TD]2[/TD]
[TD]5[/TD]
[TD]0[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Order2[/TD]
[TD]Op2[/TD]
[TD]Test[/TD]
[TD]Banana[/TD]
[TD]2[/TD]
[TD]7[/TD]
[TD]5[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Order3[/TD]
[TD]Op1[/TD]
[TD]Wash[/TD]
[TD]Apple[/TD]
[TD]2[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]Order3[/TD]
[TD]Op2[/TD]
[TD]Test[/TD]
[TD]Apple[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Order4[/TD]
[TD]Op1[/TD]
[TD]Unpeel[/TD]
[TD]Banana[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]4[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]Order4[/TD]
[TD]Op2[/TD]
[TD]Slice[/TD]
[TD]Banana[/TD]
[TD]4[/TD]
[TD]6[/TD]
[TD]3[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]Order4[/TD]
[TD]Op3[/TD]
[TD]Test[/TD]
[TD]Banana[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]8[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]Order5[/TD]
[TD]Op1[/TD]
[TD]Wash[/TD]
[TD]Orange[/TD]
[TD]4[/TD]
[TD]8[/TD]
[TD]5[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]Order5[/TD]
[TD]Op2[/TD]
[TD]Test[/TD]
[TD]Orange[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Order5[/TD]
[TD]Op3[/TD]
[TD]Unpeel[/TD]
[TD]Orange[/TD]
[TD]5[/TD]
[TD]8[/TD]
[TD]4[/TD]
[TD]9[/TD]
[/TR]
</tbody>[/TABLE]

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_Blonde]

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.

 

[nikio8]

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

 

[peter789]

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

 

[Excel_Blonde]

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.

 

[theBardd]

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.