Excel MI Reporting Query

[N.Lamech]

Hi,

I'm a bit of a novice in Excel and have a requirement for which I don't know the best approach.

PC ID Application Signed Off Username
001 app1 Yes A B
001 app2 Yes A B
002 app1 Yes A C
002 app2 Yes A C
003 app1 Yes A D
003 app3 No A D
004 app4 No A E
005 app5 No A F

I need to produce some MI that tells me which PC IDs are ready and which are not ready based on all of the applications it has being signed off. Column 3 is manually updated as applications are signed off so the results I would expect from the above would be:

PC ID Signed Off Username
001 Yes A B
002 Yes A C

I'm unsure of the best way of achieving this. In my master spreadsheet some of the PC IDs have over 400 apps so simply filtering the columns is simply not possible. The key output also is only to know which PCs have had all of the applications assigned to it signed off. Hopefully this makes some sense......any help would be much appreciated.

[Kaper]

I would go for two tools (both from Data ribbon):
1) Advanced Filter (to copy only data with Signed = Yes)
then remove column with ID Application
2) Remove Duplicates


Assuming your data is in columns A:D write in F1:
Signed
and in F2
Yes

and run the macro which dows the above actions:
Sub test()

Please Login or Register  to view this content.

See attachement book1.xls

[N.Lamech]

Hi,

Thanks for the info, nearly what I was looking for......I have just run the macro and it has output results for PC 001, 002 & 003. I only need to display the PC IDs where all of the applications are marked as signed off. As PC ID 003 has 2 applications, 1 which is signed off and 1 which isn't, I wouldnt expect to see this in the list.

Is there anyway to ensure that we filter only on PCs with all apps signed off?

[xlbiznes]

Hi,

This is my solution and its more like a database querying using SQL statements. I have also attached a sample data file in line with the way you have described.

You can try selecting the actual file from the select button and i presume, that the sheet that has the data is called "Sheet1", if not we need to replace the word Sheet1 with the actual sheet name in the sql statement.

[N.Lamech]

Hi......thanks very much, this has give me exactly what I need.

Thanks for your help.

[xlbiznes]

You are welcome. Glad i could help.

[xlbiznes]

Please mark this thread as solved, if you are happy with the solution provided.

[N.Lamech]

Just one question.......I have additional columns in the master spreadsheet. If I wanted to incldue these in the query results how do I add them into the query?

[xlbiznes]

Hi,

this is the sql statement :

Please Login or Register  to view this content.

Let us assume that you have a field Department, this would be added in these 2 places :

Please Login or Register  to view this content.

[N.Lamech]

Hi,
I have copied in as:
select a.[PC ID], [USERNAME] , [TEST] COUNT(a.[APPLICATION]) AS APP_COUNT, ( SELECT COUNT(application) FROM [SHEET1$] WHERE [SIGNED OFF] ='Yes' and [pc id] = a.[pc id] ) as Signed FROM [SHEET1$] a GROUP BY [PC ID],[USERNAME] , [TEST] having COUNT(a.[APPLICATION]) = ( SELECT COUNT(application) FROM [SHEET1$] WHERE [SIGNED OFF] ='Yes' and [pc id] = a.[pc id])

But when run I get the error 'Syntax error (missing operator) in query expression '[TEST] COUNT(a.[Application]).

Any ideas?

[xlbiznes]

Hi,

You need to put a comma after [TEST] AND COUNT(a.[APPLICATION]) AS APP_COUNT like this :

Please Login or Register  to view this content.

rest everything seems fine.

[N.Lamech]

Hi,

Thanks....I managed to work out how to add columns at the start of the worksheet but not after! The columns in order are as follows:

"PC ID" "Application" "Signed off" "PC or laptop" "user name" "HWbldng_sc" "Directorate"
"Division" "Branch" "Room" "Site"

[xlbiznes]

try this,

Please Login or Register  to view this content.

[N.Lamech]

Hi thats perfect thanks........may have hit a bit of a snag though!

I tested this on a section of the data (500 records) and it worked instantly, however the workbook has 25,000 records and when attempted with this many records it crashes! Is there anyway of speeding it up or do I just need to chop the data into manageable chunks?

[xlbiznes]

it should work fine for even 25,000 records. As i normally use this with files with more than 50,000 records.

[xlbiznes]

One more thing, when you run such queries with row intensive files it might freeze up ,but will get back after the query has finished processing.

[N.Lamech]

Hi,

Just run the query on a test set of data and unfortunately it doesnt seem to be working. I have kept in 2 example sets of users in the attached to show you.

The query picks up both of these users in the results however as you can see from the listeach user also has lots of other applications that are not ready. The requirement for the query is only to output users that have all of their apps signed off. Any ideas why this is?

[xlbiznes]

Looking into this right now.

[xlbiznes]

Hi,

when i run the query on the master.xlsx file that you have posted, no records are displayed for the condition of fully signed off.

am i missing something here ?

[N.Lamech]

Hi,
I think it may be an issue with the source data. When I run it with the source data it doesn't work however with the dummy data I provided it works!

met me try and find an example of where it doesnt work

[N.Lamech]

Hi,

I think I understand where the issue is. If you run the query on the attached data it should output the result as a record even though only 1 of the 3 applications is marked as 'Yes'. However when I change the column F so that the location is the same for all 3 records the query doesn't output the record. It seems like the query is verifying the data across multiple fields whereas these fields will in most cases be different.

Is it possible to run the query so that it only pulls up results where Column C = Yes for all applications in column b per unique user as defined in Column A!

Hope that makes sense.

[xlbiznes]

We can do a work around by taking the max for the dimension columns like building, site,HWbldng_sc . Is that ok

[xlbiznes]

Can you try this sql statement :

Please Login or Register  to view this content.

[N.Lamech]

Thanks......I'll give that a go now although it still takes a long time to run. It outputs the correct results if I delete all of the data apart from what is in Col A Col B and Col C so will check the results against that number.

[xlbiznes]

I hope we are able to get to the output that you have been trying to get so far.

[N.Lamech]

Using the new SQL statement against all the data I receive 1390 results.

Using the old SQL statement and deleting all data apart from Col A-C I get 718 result so there is still a mismatch.

Does the new SQL statement allow for different usernames per PC ID as this is the case sometimes?

[xlbiznes]

using the last sql statement , it expects that [pc id],[pc or laptop],[user name] will not change for a give pc id.

If the user name is going to vary then use this sql

Please Login or Register  to view this content.

[N.Lamech]

Hi,

718 for both now.....think we finally got there thanks very much for all your help.......much appreciated

[xlbiznes]

Finally,

You are welcome. Glad i could help.

[xlbiznes]

I you are satisfied with the solution provided, please mark this thread as solved.