Mutliple Columns Group by one column and get other columns under the grouped column

[Nmassarone]

Looking to see if someone can help with a table for an excel sheet im trying to create. I tried looking around for something similar but couldn’t find anything.

Basically I have 3 column values and want to take those values and group them by a name and then show all the IDs for that person under the Name but have them in a column by name sort of thing.

So for example I have the below set of data that is together in 1 table. The only thing is the amount of people might change 1 week it might be 2 names the next week it might be 5 names so it is never a set number of people is the only thing. Also the amount of PERSON_NUM is never set as well 1 person might have 5 numbers and another might have 1 number.

PERSON_NAME	PERSON_NUM	TYPE2_CODE
JOHN SMITH	444444	6WW
JOHN SMITH	S001234
JOHN SMITH	S002345	P1Z
JOHN SMITH	S098765
JOHN SMITH	S099887	$A1
ROGER ALLEN	S004444
ROGER ALLEN	S004555
ROGER ALLEN	S023232
ROGER ALLEN	S055555	AAK
ROGER ALLEN	S015151
ROGER ALLEN	S022222
ROGER ALLEN	S020020
ROGER ALLEN	999999	B1A
FAKE BANK UNION	888888	PP$
FAKE BANK UNION	S091919	MM$
FAKE BANK UNION	S092777	$Z1
JOHN ALLEN-SMITH	123456

And ideally what I would like to do is take each name and group it with the PERSON_NUM and TYPE2_CODE, this could be in a Reporting table as this will be exported to a tab inside an excel file

So with the above information if we could get the below as the result Basically taking the PERSON_NAME and then grouping the PERSON_NUM and TYPE2_CODE based on that name…

I have been wrapping my head around this and cant find a way to get the below any help would be appreciated.

Below is the ideal outcome that I would like to get to any help on how to get this would be amazing if this is possible.

JOHN SMITH		JOHN ALLEN-SMITH		ROGER ALLEN		FAKE BANK UNION
PERSON_NUM	TYPE2_CODE	PERSON_NUM	TYPE2_CODE	PERSON_NUM	TYPE2_CODE	PERSON_NUM	TYPE2_CODE
444444	6WW	123456		999999	B1A	888888	PP$
S001234				S004444		S091919	MM$
S002345	P1Z			S004555		S092777	$Z1
S098765				S023232
S0998877	$A1			S055555	AAK
				S015151
				S022222
				S020020

[shancmiralles]

hi! i got the output you need, unfortunately i am unable to post my wf here since my system is within company property .. but i'll list the tools i used and the configuration:

1) record id (configuration is as is , we just need the numbering) -  4 field and 17 records should show

2) transpose tool ( configuration for key columns is record id checked, data columns check all except for record id) - so now you have 3 fields/columns (RecordID, Name, Value) and 51 records 

3) from your (#1) record tool output anchor also connect a summarize tool ( configuration is group by PERSON_NAME column) output should be 1 field w/ 4 records 

4) on you (#2) transpose tool , connect a new  summarize tool ( configuration is group by Name column) output should be 1 field w/ 3 records 

5) from your (#4) summarize tool connect a Sample tool ( last 2 rows) - should only give you "PERSON_NUM" and "TYPE2_CODE".

6) using an Append Fields tool connect your #3 to the T input anchor and your #5 to the Source input anchor.   output anchor should have 2 Fields ( PERSON_NAME & Name) and 8 records displayed.

7) now connect a Cross Tab Tool to your #6 .  just Change Column Headers to "PERSON_NAME" column , Values for New Columns is "Name" column and choose "Concatenate" for Method for Aggregating values. your output should have 4 fields and 1 record  - headers should now match the one on your expected output.

8) now connect 4 Text To Columns tool all at the same time to your #7.   So use 1 Text to column tool for every Field/column. the output for each text to column should show your header with your sub headers.

9) combine all your 4 TTC tools #8  with a Join Multiple tool  - Join by Specific Fields (just connect them with the original columns from each Input  also deselect the columns unneeded and rename the second column with spaces and output anchor should now give you this:

JOHN SMITH		JOHN ALLEN-SMITH		ROGER ALLEN		FAKE BANK UNION
PERSON_NUM	TYPE2_CODE	PERSON_NUM	TYPE2_CODE	PERSON_NUM	TYPE2_CODE	PERSON_NUM	TYPE2_CODE

now we got the headers and sub headers , we go now to the body/data.  

10) combine your #1 and #9 with another Join Multiple tool  - Join by Specific Fields  (just connect them with any column from each Input)  you should have 12 fields and 18 records  displayed.

11) connect a Sort tool to your #10 and sort it with the RecordID column Ascending order.  sub headers should return back to the top.

12) connect a Formula tool to your #11. we'll have 8 formulas.  output columns should be the existing columns for the Headers (2 for each)  .. example for the "FAKE_BANK_UNION" our formula is:
        IF [PERSON_NAME]="FAKE BANK UNION" THEN [PERSON_NUM] ELSE [FAKE_BANK_UNION] ENDIF
      the next formula is for the 2nd column of the fake bank (which should show as blank but we used space instead) , and our formula is:
           IF [PERSON_NAME]="FAKE BANK UNION" THEN [TYPE2_CODE] ELSE [ ] ENDIF
     just continue this for the next 3 headers and change the variable.

           if it also happened to you (i don't know why, but something went wrong with the sub headers , i was able to remedy that with a find and replace tools later on - but for now leave it as it is )

13)  connect a select tool to your #12 and deselect the record id , person_name, person_num, and type_code columns - this should leave you with 8 fields and still 18 records displayed.

14)  now connect 4 filter tool all at the same time to your #13 . for 1 filter for each column and just choose "Is not null" for all. 

15) now connect a select tool for each filter tool #14 and  deselect all columns except for the 2 columns/fields that we filtered,  ( so if its connected to the johnsmith filter then just select the 2 columns for the john smith data).

16)  now connect a new Record ID tool for each select tool #15. 

by this time  each person name should have 3 columns : (here's a sample)

RecordID	FAKE_BANK_UNION
1	PERSON_NUM	TYPE2_CODE
2	888888	PP$
3	S091919	MM$
4	S092777	$Z1

But there are a couple that showed  "PERSON_NUM" instead of "TYPE2_CODE" - i corrected this with a find and replace tool

17) so now it seems you have 4 outputs, which i combined using the Join Multiple . join them by RecordID columns and deselect all RecordID columns .

[Nmassarone]

Thank You @shancmiralles for the detailed solution i went through and this gives the output i really appreciate your help on this it does work as stated..... the only thing is i might not have exactly 4 names sometimes it might be 2 names sometimes it might be 10 names.....

so step 8) the 4 text to column tools only works if there are exactly 4 names but sometimes i might have more might have less

this above works with the tools if the amount of people is always at 4 but looking for a solution if the amount of people changes

thank you so much for your detailed summary i really appreciate it and it got me really close i will continue to play around with this to see if i can get the results with X amount of people

[shancmiralles]

oh.. then we need a multi tool for that.. hang on.. let me post an alternative answer

[shancmiralles · Accepted solution]

1) connect a transpose  tool to your input ( configuration is as follow: Key column = person_name || data columns = persom_num & Type2_code). you should have 3 fields and 34 records displayed.
2) from your #1 connect a formula tool  and create a new column ( let's name it Column_Cat) with the following formula :   
     [PERSON_NAME]+'__'+[Name]
     data type of the new column is V_WString

3) connect a "Multi-Row Formula tool .  Choose "Create New Field" and name it "RecordID" (type : Int32) and Expression (formula) is:

IF [Row-1:PERSON_NAME]=[PERSON_NAME]
AND [Name]='TYPE2_CODE'
THEN [Row-1:RecordID]
ELSEIF [Row-1:PERSON_NAME]=[PERSON_NAME]
THEN [Row-1:RecordID]+1
ELSE 1 ENDIF

you should have 5 fields  still with 34 records 

4) connect a cross tab  to your #3. 

configuration is as ff:

Group data by these values:

RecordID

Change Column Headers:
Column_Cat

Value for New Columns:
Value

Method for Aggregating Values:
Concatenate
you should have nine(9)  fields and eight (8) reocrds displayed.

5) connect a select tool  and deselect the "RecordID" column.  You should have 8 fields and still with 8 record.

6) connect a dynamic rename tool .  Configuaration is...

Rename Mode: Remove Prefix/Sufix.

Properties:

Remove:    __PERSON_NUM

as a :  Suffix

"Warn" if any of the Fields are not renamed 

**** so your main headers should be a tad bit cleaner now.. 

7) connect a new select tool  to your #6, rename your main headers for TYPE2 with spaces (since we are copying your output sample 😀 ).  So by now, your main header should be like this:

JOHN_SMITH

JOHN_ALLEN_SMITH

ROGER_ALLEN

FAKE_BANK_UNION

 8)  connect a sample tool  to your #7 and select sample type "First N rows" wherein N=1.  You should still have 8 fields with 1 record display.

9) now we go back to your original input an connect a new select tool .   deselect the PERSON_NAME column.  This should leave you with 2 fields (PERSON_NUM & TYPE2_CODE) with 17 records.

10) connect a new sample tool  to your #9.  Configuration should be:

🔘First N Rows 

N=1

*** this should give you 2 fields with 1 record.

11) connect a Multi-Field Formula tool  to your #10.  Configuration is:

Select "All Types of" Fields:

PERSON_NUM

TYPE2_CODE

Expression:

[_CurrentFieldName_]

****your data should change to the your headers ( "PERSON_NUM" &  "TYPE2_CODE")

12) grab an Append Fields tool  .  connect your #8 to the "T" (Targets)  input anchor.  Connect your #11 to the "S" (Source).  No changes need to be applied in the configuration & you should have 10 fields with 1 record displayed .

13) connect a new Multi-Field Formula tool  to your #12.  Configuration is:

Select "All Types of" Fields:

  "KEEP ALL COLUMNS SELECTED/CHECKED"

Expression:

IF Isempty(trim([_CurrentFieldName_])) THEN
[TYPE2_CODE] ELSE [PERSON_NUM] ENDIF
*** your output anchor should have 10 fields with 1 record displayed and all data should show as: "PERSON_NUM"  & "TYPE2_CODE"  under each main headers.

14) connect  a new select tool  to your #13 and deselect the columns  "PERSON_NUM"  & "TYPE2_CODE".

15) last tool is a union tool .  connect your #7 and #14  to your union tool and hit run.. 😁

This was made by one of my mentors @mikemc1979  as we try and compare WFs using sample cases like yours.. 
hope this helps and resolves your data. 
Enjoy your WF! 😉

[Nmassarone]

WOW I'm impressed! thank you @shancmiralles  and @mikemc1979  appreciate the help on this really i was able to follow your steps and get the results!! appreciate it.

i added the workflow for anyone looking in the future, i did add a couple sort tools just to keep the data towards the top but everything is step by step as above.

thank you i really appreciate it