I created a third column with this excel formula: =IF((A3-A2)=1,“True”,FALSE) Then I sorted all the falses and found the following numbers to be wrong. Tab 1 (3047,4022,12648,36385,38863) Tab 2 (4489,10957,11170,13071,39789,41343,45079,45250) Tab 3 (66870,66870,368255,517919,541002,541002,541004)
I was able to find some missing and duplicate values in the spreadsheet. I wrote a formula that would work with all 3 sheets:
=IF(COUNTIF($A$2:$A$475112,B2)>1,“Duplicate”,IF(ISNUMBER(MATCH(B2,$A$2:$A$475112,0)), “”, “Missing”))
This formula returned a blank if the value was found, “Missing”, if the value was missing, or "Duplicate, if the value was found more than once.
These are the values I found:
I used Excel to solve the challenge. I used an IF statement to compare the value of the next value to the previous value and see if the difference was anything other than 1. This identified problem areas in the sequence that I would go and check manually. I also used a table to order the numbers in tab 3.
@Margaret_Christ I used excel to create an if function to identify numbers not in sequential order and then pulled it into tableau to find the missing and duplicate
SWAG case.twbx (5.9 MB)
values
SWAG1_SequenceChecks.xlsx (6.9 MB)
Created a sequence check to find missing and duplicate numbers, the logic was the same for all three.
This was a great challenge. I used Alteryx to solve. See screenshots below for pictures and results.
1 Like
This was a fun challenge! I know I’ve missed the deadline, but I still wanted to post the solution. I solved it using Alteryx after my laptop couldn’t handle it in excel. Here is a screenshot of my workflow as well as the packaged workflow itself. @Margaret_Christ
Weekly Challenge - SWAG.yxzp (5.5 MB)