1

2 questions: I have approx. 200 locations with equipment listing at each site. I have gateway#, name, type, location name. Most pieces of equipment are at unique locations; however, some of my locations listing are blanks or duplicate entries which I need the duplicates as that indicates more equipment. I want to sequentially add numbers in a new column to identify each site (1,2,3, etc); however, if I have 2 or more pieces of equipment with the same location name existing, I want to give each duplicate cell the same number as the others that match and continue on.(If I have 3 rows with same location name, I want the newly assigned number to be reflected in my new column).

2nd question is if the equipment location is blank, copy info from other cell and then match that name for duplicates, if not assign a new sequential number I manually accomplished what I want; however, I'd like a formula to automatically match conditions.

enter image description here

Improve this question
7
  • 1
    sounds like a few nested ifs and vlookups will work. But without test data it is hard to give a definitive answer.
    – Scott Craner
    Jul 11, 2017 at 13:33
  • first time posting. How can I add my excel sheet for reference?
    – Jon
    Jul 11, 2017 at 13:34
  • Best method, limit the data only to the data needed, mock it up and put it as text in the post. Highlight the text and hit Ctrl-k. It will format it as code. Next is to load a picture of the limited data onto a third party site then link to it from here.
    – Scott Craner
    Jul 11, 2017 at 13:36
  • 1
    Sorry I don't download files from the internet. Maybe someone who is more daring will.
    – Scott Craner
    Jul 11, 2017 at 13:38
  • (Not able to embed image so have to link to it) attached above
    – Jon
    Jul 11, 2017 at 14:01

1 Answer 1

Reset to default
1

On cell E2 enter =IFERROR(VLOOKUP(IF(D2="",B2,D2),IF(D2="",$B$1:$E1,$D$1:$E1),IF(D2="",4,2),0),MAX($E$1:E1)+1) and copy it down

enter image description here

Improve this answer
3
  • I duplicated row 18 to show that you get the same number (16) for both Jackson entries, so duplicates on column B with blanks on D share the same number
    – airstrike
    Jul 11, 2017 at 16:53
  • 1
    This is a TERRIFIC formula. It's extremely elegant. I learned a lot from this. Thank you.
    – Alex M
    Feb 21, 2019 at 23:46
  • @AlexM thank you very much! you made my day :-)
    – airstrike
    Feb 22, 2019 at 0:06

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .