I have survey data that looks like this and I want to append a uniqueid (1-3) every 3 rows of the name: Afammy Yes Afammy No Afammy Maybe JoeShmoe Who knows JoeShmoe It's possible JoeShmoe Why not My output should look like this: RecordIDNameResponse1AfammyYes2Afammy No3AfammyMaybe1JoeShmoeWho Knows2JoeShmoeIts possible3JoeShmoeWhy not RecordID tool not helpful here, can't seem to find a formula, and regex looks for patterns but in the data. I think this is a simple enough task but confounded at the moment- maybe been at it too long! Thanks for any help you can offer.

Unique ID Pattern

Afammy

I have survey data that looks like this and I want to append a uniqueid (1-3) every 3 rows of the name:

Afammy

Yes

Afammy

Maybe

JoeShmoe

Who knows

JoeShmoe

It's possible

JoeShmoe

Why not

My output should look like this:

RecordID	Name	Response
1	Afammy	Yes
2	Afammy	No
3	Afammy	Maybe
1	JoeShmoe	Who Knows
2	JoeShmoe	Its possible
3	JoeShmoe	Why not

RecordID tool not helpful here, can't seem to find a formula, and regex looks for patterns but in the data. I think this is a simple enough task but confounded at the moment- maybe been at it too long! Thanks for any help you can offer.

Preparation

Accepted answers

Kanderson

The tile tool will do this if you set the tile method to "unique value" and the unique field to the respondants name. You can also use the multi row formula tool as well.

All comments

dataMack

Use the 'Multi-row formula' tool- your formula would check the row above to see if it is null or 3 and if so, enter a 1, otherwise +1 from the value above.

Kanderson

The tile tool will do this if you set the tile method to "unique value" and the unique field to the respondants name. You can also use the multi row formula tool as well.

patrick_digan

@Afammy I'm late to the party but I would use a mod formula like this one:

MODULO([RecordID], 3)+1

You could either use use a RecordID tool followed by a formula or a Multi-Row formula.

set of 3.yxmd

Afammy

Thanks all,the multi tool would have worked also. I chose the Tile tool because I hadn't used it yet and seemed like the easiest option- now I've added another tool to my tool box. Can't say enough about the expertise and responsiveness of this community. Much appreciated!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703