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

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! 😉

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

Workflow

Datasets

Reporting

Parse

Data Investigation

Accepted answers

shancmiralles

1) connect a transpose :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 :formula: 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 :MultiRowFormula: . 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 :crosstab: to your #3.

configuration is as ff:

Group data by these values:

:checkbox: RecordID

Change Column Headers:
Column_Cat

Value for New Columns:
Value

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

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

6) connect a dynamic rename tool :dynamicrename: . 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 :Select: 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 :Sample: 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 :Select: . 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 :Sample: 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 :MultiFIeldFormula: to your #10. Configuration is:

Select "All Types of" Fields:

:checkbox: PERSON_NUM

:checkbox: TYPE2_CODE

Expression:

[_CurrentFieldName_]

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

12) grab an Append Fields tool :appendfields: . 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 :MultiFIeldFormula: to your #12. Configuration is:

Select "All Types of" Fields:

:checkbox: "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 :Select: to your #13 and deselect the columns "PERSON_NUM" & "TYPE2_CODE".

15) last tool is a union tool :Union: . 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! 😉

All comments

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

3) connect a "Multi-Row Formula tool :MultiRowFormula: . 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 :crosstab: to your #3.

configuration is as ff:

Group data by these values:

:checkbox: RecordID

Change Column Headers:
Column_Cat

Value for New Columns:
Value

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

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

6) connect a dynamic rename tool :dynamicrename: . 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 :Select: 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 :Sample: 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 :Select: . 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 :Sample: 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 :MultiFIeldFormula: to your #10. Configuration is:

Select "All Types of" Fields:

:checkbox: PERSON_NUM

:checkbox: TYPE2_CODE

Expression:

[_CurrentFieldName_]

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

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

Select "All Types of" Fields:

:checkbox: "KEEP ALL COLUMNS SELECTED/CHECKED"

Expression:

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

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

Workflow Image.png

NameAndNumFormat.yxmd

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703