0

I have an Excel dataset and I can't use any plug-ins - only Excel formulas, for the following task: There is a table: Column A, containing text labels, and Column B, containing numbers corresponding to each label (not in an ascending order). The task is: when making a new unique entry in Column A, there should be a way to find out which numbers are already taken and/or which numbers are available to be used in Column B. Please advise as to which formula to use, whether a different sheet containing all the available numbers should be made etc.

Andrea
  • 3
  • 1

1 Answers1

0

Say we already have data in cols A & B like:

enter image description here

Each unique item has its own number. We are going to make an entry in A21. The following formula will assign an old number if the item appears above and it will assign a new, unused, number if the item does not appear above:

In B21 enter:

=IF(A21="","",IF(COUNTIF($A$1:A21,A21)=1,1+MAX($B$1:B20),INDEX($B$1:B20,MATCH(A21,$A$1:A20,0))))

Naturally, you would adjust the formula to the point you want to make new entries.

Gary's Student
  • 95,722
  • 10
  • 59
  • 99
  • thanks for the reply :) Another question: what if there's a specific numbering format for the lists (eg. KL09400, KL09401 etc.) and it also happens that there are available numbers in-between some already taken ones (eg. 4 and 7 are taken, therefore you could choose between 5 and 6 for the new entry)? Basically, I am looking for a way to get the unused numbers given a list of all possible values, if that makes any sense. Is that possible in any way? – Andrea Apr 07 '20 at 13:20
  • @Andrea That would require a macro rather than a simple formula. – Gary's Student Apr 07 '20 at 13:29