Insert copies of rows based on data being non-zero in columns

[StephenJPSheehan]

Hello,

I am looking to try and enter new rows and two condensed columns for my dataset below based on certain criteria. One of the new columns would be called "County", and the contents would be either "County 1", "County 2" etc. depending on whether the company has a numeric value in that specific "County [number]" field. The other would be "Value" and this would be the numeric value that is located in the corresponding "County [number]" field.

The issue I have is that some companies have values in more than county, which means that duplicate rows will need to be created for each unique value in the "Company" field, with the only value changing being the values in the newly created "County" column. Ideally, the duplicate rows would all contain duplicates of the information in "Company Info x", "Company Info y" and "Company Info z". Please see a snapshot of the data below:

Company	Company Info x	Company Info y	Company info z	County 1	County 2	County 3	County 4	County 5	County 6	County 7	County 8	County 9	County 10	County 11	County 12	County 13	County 14	County 15	County 16	County 17
Company 4	Text	Text	Text
Company 26	Text	Text	Text	8,650,098
Company 46	Text	Text	Text					88,809,569			75,800,000
Company 61	Text	Text	Text		990,670,895	990,766,590	97,656,000	8,605,757	85,695	9,009,005	980,005	680,606	8,077,086	9,700,096		9,070,065	998,950	5,605,096
Company 107	Text	Text	Text	67,095,500	6,606	9,079,500,660	66,906,960	69,505,907	7,800,600	9,870,666,798	905,688,607	76,606,569	9,668,809,005	600,905,807	609,770,596	560,600,000	707,898,697	696,970,695	68,767,060	900,985,888

Any advice on the best place to begin on this would be greatly appreciated!

[mbarone]

Have you tried using the Transpose and/or Crosstab tool (perhaps a combination of both - but I'd start with the Transpose tool).  That would be a good starting point.

[pedrodrfaria]

Hi @StephenJPSheehan 

Please see attached the workflow. I used a transpose logic to group by certain columns and transpose the rest into rows.

Pedro.

[Qiu · Accepted solution]

@StephenJPSheehan 
@pedrodrfaria gives great answer, but would the data make more sense in this format?

[StephenJPSheehan]

Thank you all for the helpful replies!

@Qiu - the format in the output presented below is perfect for what I need, so I am happy to accept this as the solution. I note that the solution works as long as the values in the various "County [number]" fields are saved as String, which works for my data as long as I convert the data type prior to the filter being applied.

[Qiu]

@StephenJPSheehan 

Glad to help and thank you for the accept mark.