.png){kind=logo}
Alternating Row Numbers in Excel: How to Number 1-2 Efficiently
Hi Clay Support, I have a table of contacts and I need a column that gives each row a number from 1-2 where the values alternate between 1 and 2. What's the best way to achieve this?
Hi Clay Support, I have a table of contacts and I need a column that gives each row a number from 1-2 where the values alternate between 1 and 2. What's the best way to achieve this?
Create a new column with Formula input type to generate alternating 1-2 values.
To set this up:
1. Add a new column by scrolling right and selecting Add column
2. Choose Formula from the advanced actions
3. Use a formula like: IF(MOD(ROW(), 2) = 1, 1, 2) to alternate between 1 and 2 based on row position
This will automatically assign 1 to odd rows and 2 to even rows throughout your table.
When using the above formula I got the following error Syntax error at line 1 col 18: 1 IF(MOD(ROW(), 2) = 1, 1, 2) ^ Unexpected EQ token: "=". Instead, I was expecting to see one of the following: A RPAREN token based on: postfix_expression → postfix_expression _ %LPAREN _ arg_list _ ● %RPAREN unary_expression → ● postfix_expression multiplicative_expression → ● unary_expression additive_expression → ● multiplicative_expression shift_expression → ● additive_expression relational_expression → ● shift_expression equality_expression → ● relational_expression and_expression → ● equality_expression exclusive_or_expression → ● and_expression inclusive_or_expression → ● exclusive_or_expression logical_and_expression → ● inclusive_or_expression logical_or_expression → ● logical_and_expression nullish_coalescing_expression → ● logical_or_expression
Hey Kamelia, thanks for sharing this. Could you tell me more about the goal here? Do you need strict alternation? My thought would be to use a formula that selects a random number 0-1. It wouldn't alternate but would roughly average out over the whole table. The other idea is to use the round-robin features. It would take a bit more setup, but would ensure fair distribution. https://www.clay.com/university/guide/round-robin-integration-overview
Hi Mark, I need to evenly split contacts within an account. I've sorted the table by the account domain. Can you help me setup the round robin feature?
What's the ultimate goal after splitting the contacts? Could you tell me more about where you're stuck in the setup process? Would you be able to share a link to your table so we have common ground?
Of course! This is the table https://app.clay.com/workspaces/304869/workbooks/wb_0t0khjjxmzvX3NcJUVZ/tables/t_0t0mlqetTi4ZPx8WF7K/views/gv_0t0moewhpZnNAz75VZe I need to create two separate lists of prospects and the split should be done on company bases. I need the lists for a test I'm running and it's important that if an account has 10 contacts I want 5 in list 1 and 5 in list 2.
Hi Kamelia, thanks for sending the table! Let me take a look :)
I recorded this walkthrough video showing exactly how to set this up: https://cln.sh/KcXXX5cR Let me know if you have any follow-up questions!
