Need major help with cleansing data dump from Microsoft SCCM tool

[kbm5034]

Hello!

I'm beginner Excel user and since I recently started work one of my task is to look at the SCCM data that comes out of the tool on weekly basis. So every week, I get a data export from MS SCCM tool and I have a major issue with it - I feel that most of the entries is garbage, since SCCM picks up anything and everything that is installed from everyones workstations/computers (components, games, patches, product names without versions, versions without product names etc..). I'm only interested in knowing about legit software products entries that are on everyone's workstations. I can go on and on and rant about this silly SCCM data dump, but I don't want to bore anyone....instead I'll tell you what I'm envisioning in the end:

1) Be able to remove duplicate entries as soon as I load the excel sheet with every week's SCCM data dump.

2) Be able to clean Product names - For example: "?????????? WinRAR ??????????" to "WinRAR" or "HP" to "Hewlett-Packard" (If this needs manual intervention I'm fine with it)

3) Matching version number between "Product Name" and "Version columns" and limit the size of the version string. For example limit: "Quicktime 7.66.71.0" to "Quicktime 7.66.x"

4) Combining multiple entries of same products that consist same version number and be able to display ONE single entry with consolidated "instance count" numbers

5) Log / tab with unmatched products list. May be a way to get this data by filtering the cleansed list (filtering out things already reviewed and then filtering for incomplete or incorrect product name) - If I made any sense.

I would really appreciate it if someone can make my life easier. Ultimately, I want to be able to export the SCCM data dump in to this Excel based cleansing tool (Macro / formula sheet based) that will give me results with minimum intervention and save a lot of time at work.

I'm attaching a prototype of what the SCCM dump would look like - usually it's 80,000+ entries.

Thank you sooo much in advance! Hope someone out there is willing to help me!