Combine following rows

Question

Hi,

I have the below data where the name and party is always followed by the % votes and number of votes in the following row.

Constituency1Name, Party, % Votes and Number of VotesP1 PADANG BESARZahidi Zainul Abidin (BN - UMNO)P1 PADANG BESAR32.6% (15,032)P1 PADANG BESARIzizam Ibrahim (PH - PPBM)P1 PADANG BESAR29.5% (13,594)P1 PADANG BESARMokhtar Senik (PAS)P1 PADANG BESAR17.1% (7,874)P10 KUALA KEDAHAzman Ismail (PH - PKR)P10 KUALA KEDAH37.5% (36,624)P10 KUALA KEDAHMuhammad Riduan Othman (PAS)P10 KUALA KEDAH23.7% (23,143)P10 KUALA KEDAHAbdullah Hasnan Kamaruddin (BN - UMNO)P10 KUALA KEDAH19.8% (19,400)

I would like to combine the data to represent as per below.

Constituency1Name, Party, % Votes and Number of VotesP1 PADANG BESARZahidi Zainul Abidin (BN - UMNO)32.6% (15,032)P1 PADANG BESARIzizam Ibrahim (PH - PPBM)29.5% (13,594)P1 PADANG BESARMokhtar Senik (PAS)17.1% (7,874)P10 KUALA KEDAHAzman Ismail (PH - PKR)37.5% (36,624)P10 KUALA KEDAHMuhammad Riduan Othman (PAS)23.7% (23,143)P10 KUALA KEDAHAbdullah Hasnan Kamaruddin (BN - UMNO)19.8% (19,400)

Appreciate your guidance.

DataNath · Accepted Answer

Hey @JingYih_Herbalife, here's one way I'd look to go about this. Basically just looks to see if there's a '%' in the row and then assigns the next ID based on the absence. From there we can use a Summarize tool to concatenate groups of the same ID. Obviously if there's a chance that '%' will appear in other rows then let me know as this logic will fail then.

JamesCharnley · Accepted Answer

Hi @JingYih_Herbalife

Here's how I would do it. You're basically just looking to create some kind of tag in two row intervals. You could use the Mod function with Record IDs for example, or in this case the tile tool.

Then I've just summarised grouping on that tag and concatenating the string.