Help with analysing consistencies in data set

[CJW_14]

Hi All,

I was wondering if someone could help me write some vba code for an analysis I need to perform (if its even possible)

I need to identify which unit ID’s share the same “role” and“value” data based on the “order” for each “form type”.

https://i.postimg.cc/mrHxD9mk/image-1.jpg

I was thinking the output could look something like the below with the org units that share the same data displayed in a comma separated list.

https://i.postimg.cc/8zx3q5TS/image-2.jpg

But if there’s an easier way id be more than happy to change the output. Id probably be looking at performing this function for 15’000 rows of data max.

I have attached an example file with 2 sheets with the data and the output.

Any help would be much appreciated.

[tttin20]

Dear CJW_14,

If you need an output without writing VBA Code, try look "Output 2" sheet in the attached file. You can use "pivot" and "Text to Columns" functions in Excel for analsysing.

Regard!

[CJW_14]

Hi tttin20,

Thanks heaps for reply, appreciate it. the output is on the right track but im looking for a solution that can output the unique combinations onto a single row if that makes sense.

[tttin20]

Dear CJW_14,

I wrote some code and the unique combinations are different from your output but still in a single row. Try Click the "RUN" button and get the result.

P/S: I's not make up the code yet.

Regard!

[CJW_14]

Hi tttin20,

Thanks alot for your time creating this :) , Ill have a play with it. Its close but not grouping the output quite right. When you look at the output vs source:

Output says 10000001,10000002,10000003 units only contain Employee/blank for leave.

https://i.postimg.cc/vH8bD9rH/2018-11-04-142005.jpg

But the source data actually has more records (orders) associated with that unit/ form type. Basically its workflow data, the order column is a recipient. So im trying to determine which units share the same routing path and show that on a single row :)