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.

  • How do I format my code blocks? – Cyrus Oct 27 '19 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 – steeldriver Oct 27 '19 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. – Manoj Kumar Oct 27 '19 at 15:41
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
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.