cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
BB-82
New Member

Create Unique ID based on item name and row number

Hi - hopefully this question makes sense -

 

I have an excel sheet I'm using with no bother that I am in the process of recreating in powerapp.    Unfortunately, there was a formula I had to delete as powerapp wouldn't upload my data tables with any formulas present.  I now cannot recreate this formula inside power app and it's causing a right headache!

 

The formula is designed to assign a new number for each unit entered, but it is designed to give a new, incremental (and sequential) number for each unit entry.  So I can measure how many entries per unit not how many in total (again sorry - I hope the chart below makes sense).

 

For business reasons I'm just going to use simple terms below.  There are a number of other free text data cards and drop downs that will get filled in by each user so I will expect to see multiple entries for the items listed below, but for different reasons.  I will have somewhere in the range of 20-30 different unit types that will get entered, some regularly, some pretty rarely.

Table name "Colours"

Column A - "Colours"

Column C - "Colour Number"

 

Column A        Column C

Blue                 001

Red                  001

Purple              001

Blue                 002

Green              001

Red                 002

Blue                003

 

As the sheet started with no entries i used an iferror(lookup formula to ensure that each initial entry for a unit gets assigned the number 1 and then each subsequent entries go up sequentially - again this works a treat in excel - but it was dependant on looking at the row number of the current entry, to focus on the rows above for any previous entries and the last number entered against it to generate a new one.

 

The formula works so well that I'm really hoping there's a solution out there to take it into powerapps.  This is just a random row selected to show what it is looking for/at.

=IFERROR(LOOKUP(2,1/($A$2:A13=A14),$C$2:C13)+1,1)

 

Thank you in advance!

0 REPLIES 0

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Power Apps June 2021

June Power Apps Community Call

Don't miss the call this month on June 16th - 8a PDT

Top Solution Authors
Top Kudoed Authors
Users online (59,388)