0

I have a column in my excel as below:

REF
---------
BV1013_1
BV1013_1
BV1013_2
BV1013_2
BV1026
BV1040
BV1040
BV1041
BV1041
BV1042
BV1042

Desired OUTPUT: Some of the cell values/names are same at several places. I would like to rename my cell names as below based on their number of occurrences.

REF             Desire
-----------------------
BV1013_1    BV1013_1_1  
BV1013_1    BV1013_1_2
BV1013_2    BV1013_2_1  
BV1013_2    BV1013_2_2
BV1026      BV1026_1
BV1040      BV1040_1
BV1040      BV1040_2
BV1041      BV1041_1
BV1041      BV1041_2
BV1042      BV1042_1
BV1042      BV1042_2

I would like to solve this in excel or using bash scripting. Any help is very valuable for me.

3
  • How do I format my code blocks?
    – Cyrus
    Oct 27, 2019 at 13:52
  • Excel spreadsheets aren't easily manipulated via bash scripts - if you convert your data to text or CSV then you could easily use something like awk though e.g. awk -F, '{$1 = sprintf("%s_%d",$1,++seen[$1])} 1' ref.csv Oct 27, 2019 at 14:24
  • Hi steeldriver, I have changed the format to .csv. Can you please explain how does it work exactly (awk -F, '{$1 = sprintf("%s_%d",$1,++seen[$1])} 1' ref.csv), i have tried this not getting the desired output. Oct 27, 2019 at 15:41

1 Answer 1

1

Using Miller (https://github.com/johnkerl/miller) starting from

REF
BV1013_1
BV1013_1
BV1013_2
BV1013_2
BV1026
BV1040
BV1040
BV1041
BV1041
BV1042
BV1042

and running

mlr --csv step -a counter -f REF -g REF then put '$Desire=$REF."_".$REF_counter' then cut -x -f REF_counter input.csv

you will have

REF,Desire
BV1013_1,BV1013_1_1
BV1013_1,BV1013_1_2
BV1013_2,BV1013_2_1
BV1013_2,BV1013_2_2
BV1026,BV1026_1
BV1040,BV1040_1
BV1040,BV1040_2
BV1041,BV1041_1
BV1041,BV1041_2
BV1042,BV1042_1
BV1042,BV1042_2

You must log in to answer this question.

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