substituting repetitive values

[Inactive User]

Hello  I have an excel file with thousands of records. I have the invoice numbers of the left in the table below. I need to compare the records and wherever the invoice numbers are repeating, I need to substitute with a symbol.  For example 1050058722/23/24/27/33/35/36 are all exclusive numbers hence the 1 against them. 1050058738 is repeating thrice hence 1 on the first row and - and - on the same numbers that follow.  How do i get this done in Alteryx. I am just a Beginner  

1050058722 - 1

1050058723 - 1

1050058724 - 1

1050058727 - 1

1050058735 - 1

1050058736 - 1

1050058738 - 1 Abhii 

[surajmthomas]

You would need to use the summarize tool get the count and then proceed. It would be helpful if you attach a sample data set in .xlsx or .csv format and also give a snap shot of how you would want your output to look like 

[surajmthomas]

Not sure if this is what you're looking for 

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Return-quot-0-quot-on-multiple-match-b... 

[AkimasaKajitani]

Hi @Inactive User 

If the input is as follows, you make the workflow of sorting and using Multi-Row formula and concatenating the fields to achieve your requirement.

The important point is using Multi-Row Formula. In Multi-Row formula, you can compare between the previous row and current row and if they are the same, you can count up the value of count up field.

IF [Row-1:Field1]=[Field1] THEN [Row-1:RepititionNo]+1 ELSE 1 ENDIF

I attached the sample workflow. Please check it.

[Qiu · Accepted solution]

@Inactive User 
Maybe something like this?

[Inactive User]

Thank you everybody. I appreciate the responses and i found more than 1 way to do this. 

@Qiu @AkimasaKajitani both your solutions worked perfectly. Thanks a lot. 

[Inactive User]

Thank you again. Adding on the below data, I need to add the amounts in the repeated invoices on the first row. Here is sample expected and i have added the excel file this time too. 

[atcodedog05 · Accepted solution]

Hi @Inactive User 

Here is how you can do it. My approach is similar to @AkimasaKajitani's approach. What I could understand is 1st occurrence and last occurrence should be displayed and rest changed to "-"

Workflow:

1. Using sort tool to sort record ID by descending. I am doing this because we need the total in the first occurrence row.

2. Using running total group by on invoice. This way we get the sum.

3. Using sort tool sort record ID by ascending. Reverting it back.

4. Using multi-row formula tool I am checking if the above invoice same as the current invoice if not it's the first row. If below invoice is not same as current invoice its last row. And flag them as 1 rest as "-"

5. Using formula to keep first and last occurrence and replace all with "-"

Hope this helps 🙂

[Inactive User]

That worked like a dream, thank you @atcodedog05