0

Good Day people,

I need a help with excel. Not sure whether I can create a macro or try in R to achieve the result that I am looking for.

I have an excel file where a certain column (not the first column in the original file) will have repeating companyid.(Initial data image below).

The expected result is that if i have repetition in the companyid, then I want to create new columns. The number of columns depend on the repetition, say if i have one repetition then I need 2 new columns (like rangestart.1,rangeend.1) and if it is 2 then 4 new columns and so on.

The end result should be that I should have only one row for that company but the data is now captured in a new column, which is dynamically named as shown below. Please note that not all companies will have repetition.

A lot of people have talked about pivot_wider option in R but I am unable to use it because of the error i am getting:

Error: package or namespace load failed for ‘tidyr’ in loadNamespace(i, c(lib.loc, .libPaths()), versionCheck = vI[[i]]):
 namespace ‘rlang’ 0.4.0 is already loaded, but >= 0.4.10 is required

Initial Data

Expected Result

Can I do something in VBA macro or R to achieve this? I tried my best with macro, though i could merge the data into one row the naming of the new columns was not right, as in it is either not dynamic or the new column names will destroy existing columns.

Note: Please let me know if there is any alternative to pivot_wider in R?

EDITED: Thank you so much for the reference. I tried all that is mentioned. I am still unable to get both the below mentioned libraries but I am getting the below error. Error in loadNamespace(i, c(lib.loc, .libPaths()), versionCheck = vI[[i]]) : namespace 'rlang' 1.0.2 is being loaded, but >= 1.0.6 is required

To get tidyr I need lifecycle but for lifecycle in need rlang but though i tried uninstall and install the latest verison of rlang I am still getting the above error. Please help. Is there any alternative to pivot_wider?

abidfayaz
  • 1
  • 1
  • See here about fixing your error with `rlang`: https://stackoverflow.com/questions/66782751/namespace-rlang-0-4-5-is-being-loaded-but-0-4-10-is-required – jrcalabrese Mar 30 '23 at 16:10
  • Thank you so much for the reference. I tried all that is mentioned. I am still unable to get both the below mentioned libraries but I am getting the below error. Error in loadNamespace(i, c(lib.loc, .libPaths()), versionCheck = vI[[i]]) : namespace 'rlang' 1.0.2 is being loaded, but >= 1.0.6 is required To get tidyr I need lifecycle but for lifecycle in need rlang but though i tried uninstall and install the latest verison of rlang I am still getting the above error. Please help. Is there any alternative to pivot_wider? – abidfayaz Mar 31 '23 at 14:39
  • `reshape2::melt` is an alterntive to `pivot_longer`, but using the `tidyr` and `dplyr` packages will likely be important for you in the future. What versions of R and RStudio are you running? – jrcalabrese Mar 31 '23 at 17:21

0 Answers0