CODEWITHSUNDEEP
vbaexcel
ImCoder
ImCoder

Reputation: 1065

Column values to be replaced with another column's values

I have an Excel file with '|' separated values in it: 

20120615|User 1|mak||ToBeReplaced|20150114
20120615|User 1|mak||ToBeReplaced|20150115
20120615|User 1|mak||ToBeReplaced|20150116
20120615|User 2|mak||ToBeReplaced|20150114
20120615|User 2|mak||ToBeReplaced|20150115
20120615|User 2|mak||ToBeReplaced|20150116
20120615|User 3|mak||ToBeReplaced|20150114
20120615|User 3|mak||ToBeReplaced|20150115
20120615|User 3|mak||ToBeReplaced|20150116

I also have another Excel spreadsheet that has the names of all Managers. Eg

Manager1
Manager2
Manager3
Manager4

Now I want the Managers names to be replaced with the column values ToBeReplaced for each User. i.e

20120615|User 1|mak||Manager1|20150114
20120615|User 1|mak||Manager1|20150115
20120615|User 1|mak||Manager1|20150116
20120615|User 2|mak||Manager2|20150114
20120615|User 2|mak||Manager2|20150115
20120615|User 2|mak||Manager2|20150116
20120615|User 3|mak||Manager3|20150114
20120615|User 3|mak||Manager3|20150115
20120615|User 3|mak||Manager3|20150116

That should go on replacing for all Users, the use case is

  1. If all Managers names are replaced and still more users are remaining, then it should start again from Manager1.

Can anyone help me with an Excel formula or something to get the desired result?

Upvotes: 1

Views: 94

Answers (2)

PatricK
PatricK

Reputation: 6433

Something like this?
Sample

Column A are your text strings.
B2 formula: User (with the white space at the end)
C2 formula: =MID(A2,SEARCH(B2,A2)+LEN(B2),SEARCH("|mak|",A2)-SEARCH(B2,A2)-LEN(B2))
D2 formula: ToBeReplaced
E2 formula: =CONCATENATE("Manager",C2)
F2 formula: =REPLACE(A2,SEARCH(D2,A2),LEN(D2),E2)

Upvotes: 1

pnuts
pnuts

Reputation: 59440

Split Text to Columns with pipe as the delimiter and then assuming the first user1 is in B2 and with 0 in E1 you might put in E2: 

=IF(B1=B2,E1,"Manager"&RIGHT(E1,1)+1+IF(RIGHT(E1,1)="4",-4))

copy down to suit, then use the output to look up actual names in a table from your other sheet.

Upvotes: 0

Related Questions