Multiple find replace with count of replacement made

rohansnaik · Jan 12, 2014

As You can See i have Data in Sheet 1 which contains sites which in string and in Sheet 2 i have site list i wanted to add one more | Pipe sign in site list ,So When I run macro it should give me result in Sheet 3 with All data with || (2Pipe sign) and Report of count of replacement done of every sites and if no of replacement is 0 than it should show replacement count as 0

___________________________________________________________________________________________________
I have Data in Sheet1 as shown below

1234|ABCD1|XYZ1|ABCDEF1|GHUIJK1|AB1|yahoo.com|12345|ABC@yahoo.com

1235|EFGH2|STU1|ABCDEF2|GHUIJK11|BC1|gmail.com|12346|JCD@yahoo.com

1236|ABCD2|XYZ2|ABCDEF3|GHUIJK21|DC1|mail.com|12347|ABC@yahoo.com

1237|EFGH3|STU2|ABCDEF4|GHUIJK31|JC1|indiatimes.com|12348|JCD@yahoo.com

1238|ABCD3|XYZ2|ABCDEF5|GHUIJK41|AB2|timesofindia.com|12349|ABC@yahoo.com

1239|EFGH4|STU2|ABCDEF6|GHUIJK51|BC2|wechat.com|12350|JCD@yahoo.com

1240|ABCD4|XYZ3|ABCDEF7|GHUIJK61|DC2|radiomirchi.com|12351|ABC@yahoo.com

1241|EFGH5|STU3|ABCDEF8|GHUIJK71|JC2|clickonce.com|12352|JCD@yahoo.com

1242|ABCD5|XYZ3|ABCDEF9|GHUIJK81|AB3|ragaa.com|12353|ABC@yahoo.com

1243|EFGH6|STU3|ABCDEF10|GHUIJK91|BC3|slice.com|12354|JCD@yahoo.com

1244|ABCD6|XYZ4|ABCDEF11|GHUIJK101|DC3|fanta.com|12355|ABC@yahoo.com

1245|EFGH7|STU4|ABCDEF12|GHUIJK111|JC3|coke.com|12356|JCD@yahoo.com

1246|ABCD7|XYZ4|ABCDEF13|GHUIJK121|AB4|thumpsup.com|12357|ABC@yahoo.com

1247|EFGH8|STU4|ABCDEF14|GHUIJK131|BC4|lyrics.com|12358|JCD@yahoo.com

1248|ABCD2|XYZ2|ABCDEF15|GHUIJK141|AB2|yahoo.com|12359|ABC@yahoo.com

1249|EFGH3|STU2|ABCDEF16|GHUIJK151|BC2|gmail.com|12360|JCD@yahoo.com

1250|ABCD3|XYZ3|ABCDEF17|GHUIJK161|DC2|mail.com|12361|ABC@yahoo.com

1251|EFGH4|STU3|ABCDEF18|GHUIJK171|JC2|indiatimes.com|12362|JCD@yahoo.com

1252|ABCD4|XYZ3|ABCDEF19|GHUIJK181|AB3|timesofindia.com|12363|ABC@yahoo.com

1253|EFGH5|STU3|ABCDEF20|GHUIJK191|BC3|wechat.com|12364|JCD@yahoo.com

1254|ABCD5|XYZ4|ABCDEF21|GHUIJK201|DC3|radiomirchi.com|12365|ABC@yahoo.com

1255|EFGH6|STU4|ABCDEF22|GHUIJK211|JC3|clickonce.com|12366|JCD@yahoo.com

1256|ABCD6|XYZ4|ABCDEF23|GHUIJK221|AB4|ragaa.com|12367|ABC@yahoo.com

1257|EFGH7|STU4|ABCDEF24|GHUIJK231|BC4|slice.com|12368|JCD@yahoo.com

1258|ABCD7|XYZ5|ABCDEF25|GHUIJK241|DC4|fanta.com|12369|ABC@yahoo.com

1259|EFGH8|STU5|ABCDEF26|GHUIJK251|JC4|coke.com|12370|JCD@yahoo.com

1260|ABCD8|XYZ5|ABCDEF27|GHUIJK261|AB5|thumpsup.com|12371|ABC@yahoo.com

1261|EFGH9|STU5|ABCDEF28|GHUIJK271|BC5|lyrics.com|12372|JCD@yahoo.com

1262|ABCD3|XYZ3|ABCDEF29|GHUIJK281|AB3|yahoo.com|12373|ABC@yahoo.com

1263|EFGH4|STU3|ABCDEF30|GHUIJK291|BC3|gmail.com|12374|JCD@yahoo.com

1264|ABCD4|XYZ4|ABCDEF31|GHUIJK301|DC3|mail.com|12375|ABC@yahoo.com

1265|EFGH5|STU4|ABCDEF32|GHUIJK311|JC3|indiatimes.com|12376|JCD@yahoo.com

1266|ABCD5|XYZ4|ABCDEF33|GHUIJK321|AB4|timesofindia.com|12377|ABC@yahoo.com

1267|EFGH6|STU4|ABCDEF34|GHUIJK331|BC4|wechat.com|12378|JCD@yahoo.com

1268|ABCD6|XYZ5|ABCDEF35|GHUIJK341|DC4|radiomirchi.com|12379|ABC@yahoo.com

1269|EFGH7|STU5|ABCDEF36|GHUIJK351|JC4|clickonce.com|12380|JCD@yahoo.com

1270|ABCD7|XYZ5|ABCDEF37|GHUIJK361|AB5|ragaa.com|12381|ABC@yahoo.com

1271|EFGH8|STU5|ABCDEF38|GHUIJK371|BC5|slice.com|12382|JCD@yahoo.com

1272|ABCD8|XYZ6|ABCDEF39|GHUIJK381|DC5|fanta.com|12383|ABC@yahoo.com

1273|EFGH9|STU6|ABCDEF40|GHUIJK391|JC5|coke.com|12384|JCD@yahoo.com

1274|ABCD9|XYZ6|ABCDEF41|GHUIJK401|AB6|thumpsup.com|12385|ABC@yahoo.com

1275|EFGH10|STU6|ABCDEF42|GHUIJK411|BC6|lyrics.com|12386|JCD@yahoo.com

1276|ABCD4|XYZ4|ABCDEF43|GHUIJK421|AB4|yahoo.com|12387|ABC@yahoo.com

1277|EFGH5|STU4|ABCDEF44|GHUIJK431|BC4|gmail.com|12388|JCD@yahoo.com

1278|ABCD5|XYZ5|ABCDEF45|GHUIJK441|DC4|mail.com|12389|ABC@yahoo.com

1279|EFGH6|STU5|ABCDEF46|GHUIJK451|JC4|indiatimes.com|12390|JCD@yahoo.com

1280|ABCD6|XYZ5|ABCDEF47|GHUIJK461|AB5|timesofindia.com|12391|ABC@yahoo.com

1281|EFGH7|STU5|ABCDEF48|GHUIJK471|BC5|wechat.com|12392|JCD@yahoo.com

1282|ABCD7|XYZ6|ABCDEF49|GHUIJK481|DC5|radiomirchi.com|12393|ABC@yahoo.com

1283|EFGH8|STU6|ABCDEF50|GHUIJK491|JC5|clickonce.com|12394|JCD@yahoo.com

1284|ABCD8|XYZ6|ABCDEF51|GHUIJK501|AB6|ragaa.com|12395|ABC@yahoo.com

1285|EFGH9|STU6|ABCDEF52|GHUIJK511|BC6|slice.com|12396|JCD@yahoo.com

1286|ABCD9|XYZ7|ABCDEF53|GHUIJK521|DC6|fanta.com|12397|ABC@yahoo.com

1287|EFGH10|STU7|ABCDEF54|GHUIJK531|JC6|coke.com|12398|JCD@yahoo.com

1288|ABCD10|XYZ7|ABCDEF55|GHUIJK541|AB7|thumpsup.com|12399|ABC@yahoo.com

1289|EFGH11|STU7|ABCDEF56|GHUIJK551|BC7|lyrics.com|12400|JCD@yahoo.com

1290|ABCD5|XYZ5|ABCDEF57|GHUIJK561|AB5|yahoo.com|12401|ABC@yahoo.com

1291|EFGH6|STU5|ABCDEF58|GHUIJK571|BC5|gmail.com|12402|JCD@yahoo.com

1292|ABCD6|XYZ6|ABCDEF59|GHUIJK581|DC5|mail.com|12403|ABC@yahoo.com

1293|EFGH7|STU6|ABCDEF60|GHUIJK591|JC5|indiatimes.com|12404|JCD@yahoo.com

1294|ABCD7|XYZ6|ABCDEF61|GHUIJK601|AB6|timesofindia.com|12405|ABC@yahoo.com

1295|EFGH8|STU6|ABCDEF62|GHUIJK611|BC6|wechat.com|12406|JCD@yahoo.com

1296|ABCD8|XYZ7|ABCDEF63|GHUIJK621|DC6|radiomirchi.com|12407|ABC@yahoo.com

1297|EFGH9|STU7|ABCDEF64|GHUIJK631|JC6|clickonce.com|12408|JCD@yahoo.com

1298|ABCD9|XYZ7|ABCDEF65|GHUIJK641|AB7|ragaa.com|12409|ABC@yahoo.com

1299|EFGH10|STU7|ABCDEF66|GHUIJK651|BC7|slice.com|12410|JCD@yahoo.com

1300|ABCD10|XYZ8|ABCDEF67|GHUIJK661|DC7|fanta.com|12411|ABC@yahoo.com

1301|EFGH11|STU8|ABCDEF68|GHUIJK671|JC7|coke.com|12412|JCD@yahoo.com

1302|ABCD11|XYZ8|ABCDEF69|GHUIJK681|AB8|thumpsup.com|12413|ABC@yahoo.com

_________________________________________________________________________________________________
I have Data in Sheet 2 as

yahoo.com	\|yahoo.com
gmail.com	\|gmail.com
mail.com	\|mail.com
indiatimes.com	\|indiatimes.com
timesofindia.com	\|timesofindia.com
wechat.com	\|wechat.com
radiomirchi.com	\|radiomirchi.com
clickonce.com	\|clickonce.com
ragaa.com	\|ragaa.com
slice.com	\|slice.com
fanta.com	\|fanta.com
coke.com	\|coke.com
thumpsup.com	\|thumpsup.com
lyrics.com	\|lyrics.com

_____________________________________________________________________________________________________________________________________
I want Output in Sheet 3 as

Output Sheet				Site	Duplicate Count
1234\|ABCD1\|XYZ1\|ABCDEF1\|GHUIJK1\|AB1\|\|yahoo.com\|12345\|ABC@\|yahoo.com		yahoo.com	1295
1235\|EFGH2\|STU1\|ABCDEF2\|GHUIJK11\|BC1\|\|g\|mail.com\|12346\|JCD@\|yahoo.com	gmail.com	87
1236\|ABCD2\|XYZ2\|ABCDEF3\|GHUIJK21\|DC1\|\|mail.com\|12347\|ABC@\|yahoo.com		mail.com	174
1237\|EFGH3\|STU2\|ABCDEF4\|GHUIJK31\|JC1\|indiatimes.com\|12348\|JCD@\|yahoo.com	indiatimes.com	0
1238\|ABCD3\|XYZ2\|ABCDEF5\|GHUIJK41\|AB2\|timesofindia.com\|12349\|ABC@\|yahoo.com	timesofindia.com	1295
1239\|EFGH4\|STU2\|ABCDEF6\|GHUIJK51\|BC2\|wechat.com\|12350\|JCD@\|yahoo.com	wechat.com	87
1240\|ABCD4\|XYZ3\|ABCDEF7\|GHUIJK61\|DC2\|radiomirchi.com\|12351\|ABC@\|yahoo.com	radiomirchi.com	174
1241\|EFGH5\|STU3\|ABCDEF8\|GHUIJK71\|JC2\|clickonce.com\|12352\|JCD@\|yahoo.com	clickonce.com	0
1242\|ABCD5\|XYZ3\|ABCDEF9\|GHUIJK81\|AB3\|ragaa.com\|12353\|ABC@\|yahoo.com		ragaa.com	1295
1243\|EFGH6\|STU3\|ABCDEF10\|GHUIJK91\|BC3\|slice.com\|12354\|JCD@\|yahoo.com		slice.com	87
1244\|ABCD6\|XYZ4\|ABCDEF11\|GHUIJK101\|DC3\|fanta.com\|12355\|ABC@\|yahoo.com	fanta.com	174
1245\|EFGH7\|STU4\|ABCDEF12\|GHUIJK111\|JC3\|coke.com\|12356\|JCD@\|yahoo.com		coke.com	0
1246\|ABCD7\|XYZ4\|ABCDEF13\|GHUIJK121\|AB4\|thumpsup.com\|12357\|ABC@\|yahoo.com	thumpsup.com	174
1247\|EFGH8\|STU4\|ABCDEF14\|GHUIJK131\|BC4\|lyrics.com\|12358\|JCD@\|yahoo.com	lyrics.com	0
1248\|ABCD2\|XYZ2\|ABCDEF15\|GHUIJK141\|AB2\|\|yahoo.com\|12359\|ABC@\|yahoo.com
1249\|EFGH3\|STU2\|ABCDEF16\|GHUIJK151\|BC2\|\|g\|mail.com\|12360\|JCD@\|yahoo.com
1250\|ABCD3\|XYZ3\|ABCDEF17\|GHUIJK161\|DC2\|\|mail.com\|12361\|ABC@\|yahoo.com
1251\|EFGH4\|STU3\|ABCDEF18\|GHUIJK171\|JC2\|indiatimes.com\|12362\|JCD@\|yahoo.com
1252\|ABCD4\|XYZ3\|ABCDEF19\|GHUIJK181\|AB3\|timesofindia.com\|12363\|ABC@\|yahoo.com
1253\|EFGH5\|STU3\|ABCDEF20\|GHUIJK191\|BC3\|wechat.com\|12364\|JCD@\|yahoo.com
1254\|ABCD5\|XYZ4\|ABCDEF21\|GHUIJK201\|DC3\|radiomirchi.com\|12365\|ABC@\|yahoo.com
1255\|EFGH6\|STU4\|ABCDEF22\|GHUIJK211\|JC3\|clickonce.com\|12366\|JCD@\|yahoo.com
1256\|ABCD6\|XYZ4\|ABCDEF23\|GHUIJK221\|AB4\|ragaa.com\|12367\|ABC@\|yahoo.com
1257\|EFGH7\|STU4\|ABCDEF24\|GHUIJK231\|BC4\|slice.com\|12368\|JCD@\|yahoo.com
1258\|ABCD7\|XYZ5\|ABCDEF25\|GHUIJK241\|DC4\|fanta.com\|12369\|ABC@\|yahoo.com
1259\|EFGH8\|STU5\|ABCDEF26\|GHUIJK251\|JC4\|coke.com\|12370\|JCD@\|yahoo.com
1260\|ABCD8\|XYZ5\|ABCDEF27\|GHUIJK261\|AB5\|thumpsup.com\|12371\|ABC@\|yahoo.com
1261\|EFGH9\|STU5\|ABCDEF28\|GHUIJK271\|BC5\|lyrics.com\|12372\|JCD@\|yahoo.com
1262\|ABCD3\|XYZ3\|ABCDEF29\|GHUIJK281\|AB3\|\|yahoo.com\|12373\|ABC@\|yahoo.com
1263\|EFGH4\|STU3\|ABCDEF30\|GHUIJK291\|BC3\|\|g\|mail.com\|12374\|JCD@\|yahoo.com
1264\|ABCD4\|XYZ4\|ABCDEF31\|GHUIJK301\|DC3\|\|mail.com\|12375\|ABC@\|yahoo.com
1265\|EFGH5\|STU4\|ABCDEF32\|GHUIJK311\|JC3\|indiatimes.com\|12376\|JCD@\|yahoo.com
1266\|ABCD5\|XYZ4\|ABCDEF33\|GHUIJK321\|AB4\|timesofindia.com\|12377\|ABC@\|yahoo.com
1267\|EFGH6\|STU4\|ABCDEF34\|GHUIJK331\|BC4\|wechat.com\|12378\|JCD@\|yahoo.com
1268\|ABCD6\|XYZ5\|ABCDEF35\|GHUIJK341\|DC4\|radiomirchi.com\|12379\|ABC@\|yahoo.com
1269\|EFGH7\|STU5\|ABCDEF36\|GHUIJK351\|JC4\|clickonce.com\|12380\|JCD@\|yahoo.com
1270\|ABCD7\|XYZ5\|ABCDEF37\|GHUIJK361\|AB5\|ragaa.com\|12381\|ABC@\|yahoo.com
1271\|EFGH8\|STU5\|ABCDEF38\|GHUIJK371\|BC5\|slice.com\|12382\|JCD@\|yahoo.com
1272\|ABCD8\|XYZ6\|ABCDEF39\|GHUIJK381\|DC5\|fanta.com\|12383\|ABC@\|yahoo.com
1273\|EFGH9\|STU6\|ABCDEF40\|GHUIJK391\|JC5\|coke.com\|12384\|JCD@\|yahoo.com
1274\|ABCD9\|XYZ6\|ABCDEF41\|GHUIJK401\|AB6\|thumpsup.com\|12385\|ABC@\|yahoo.com
1275\|EFGH10\|STU6\|ABCDEF42\|GHUIJK411\|BC6\|lyrics.com\|12386\|JCD@\|yahoo.com
1276\|ABCD4\|XYZ4\|ABCDEF43\|GHUIJK421\|AB4\|\|yahoo.com\|12387\|ABC@\|yahoo.com
1277\|EFGH5\|STU4\|ABCDEF44\|GHUIJK431\|BC4\|\|g\|mail.com\|12388\|JCD@\|yahoo.com
1278\|ABCD5\|XYZ5\|ABCDEF45\|GHUIJK441\|DC4\|\|mail.com\|12389\|ABC@\|yahoo.com
1279\|EFGH6\|STU5\|ABCDEF46\|GHUIJK451\|JC4\|indiatimes.com\|12390\|JCD@\|yahoo.com
1280\|ABCD6\|XYZ5\|ABCDEF47\|GHUIJK461\|AB5\|timesofindia.com\|12391\|ABC@\|yahoo.com
1281\|EFGH7\|STU5\|ABCDEF48\|GHUIJK471\|BC5\|wechat.com\|12392\|JCD@\|yahoo.com
1282\|ABCD7\|XYZ6\|ABCDEF49\|GHUIJK481\|DC5\|radiomirchi.com\|12393\|ABC@\|yahoo.com
1283\|EFGH8\|STU6\|ABCDEF50\|GHUIJK491\|JC5\|clickonce.com\|12394\|JCD@\|yahoo.com
1284\|ABCD8\|XYZ6\|ABCDEF51\|GHUIJK501\|AB6\|ragaa.com\|12395\|ABC@\|yahoo.com
1285\|EFGH9\|STU6\|ABCDEF52\|GHUIJK511\|BC6\|slice.com\|12396\|JCD@\|yahoo.com
1286\|ABCD9\|XYZ7\|ABCDEF53\|GHUIJK521\|DC6\|fanta.com\|12397\|ABC@\|yahoo.com
1287\|EFGH10\|STU7\|ABCDEF54\|GHUIJK531\|JC6\|coke.com\|12398\|JCD@\|yahoo.com
1288\|ABCD10\|XYZ7\|ABCDEF55\|GHUIJK541\|AB7\|thumpsup.com\|12399\|ABC@\|yahoo.com
1289\|EFGH11\|STU7\|ABCDEF56\|GHUIJK551\|BC7\|lyrics.com\|12400\|JCD@\|yahoo.com
1290\|ABCD5\|XYZ5\|ABCDEF57\|GHUIJK561\|AB5\|\|yahoo.com\|12401\|ABC@\|yahoo.com
1291\|EFGH6\|STU5\|ABCDEF58\|GHUIJK571\|BC5\|\|g\|mail.com\|12402\|JCD@\|yahoo.com
1292\|ABCD6\|XYZ6\|ABCDEF59\|GHUIJK581\|DC5\|\|mail.com\|12403\|ABC@\|yahoo.com
1293\|EFGH7\|STU6\|ABCDEF60\|GHUIJK591\|JC5\|indiatimes.com\|12404\|JCD@\|yahoo.com
1294\|ABCD7\|XYZ6\|ABCDEF61\|GHUIJK601\|AB6\|timesofindia.com\|12405\|ABC@\|yahoo.com
1295\|EFGH8\|STU6\|ABCDEF62\|GHUIJK611\|BC6\|wechat.com\|12406\|JCD@\|yahoo.com
1296\|ABCD8\|XYZ7\|ABCDEF63\|GHUIJK621\|DC6\|radiomirchi.com\|12407\|ABC@\|yahoo.com
1297\|EFGH9\|STU7\|ABCDEF64\|GHUIJK631\|JC6\|clickonce.com\|12408\|JCD@\|yahoo.com
1298\|ABCD9\|XYZ7\|ABCDEF65\|GHUIJK641\|AB7\|ragaa.com\|12409\|ABC@\|yahoo.com
1299\|EFGH10\|STU7\|ABCDEF66\|GHUIJK651\|BC7\|slice.com\|12410\|JCD@\|yahoo.com
1300\|ABCD10\|XYZ8\|ABCDEF67\|GHUIJK661\|DC7\|fanta.com\|12411\|ABC@\|yahoo.com
1301\|EFGH11\|STU8\|ABCDEF68\|GHUIJK671\|JC7\|coke.com\|12412\|JCD@\|yahoo.com
1302\|ABCD11\|XYZ8\|ABCDEF69\|GHUIJK681\|AB8\|thumpsup.com\|12413\|ABC@\|yahoo.com

Rick Rothstein · Jan 12, 2014

Give this macro a try...

Code:

Sub DoubleUpVerticalBarsAndCountSites()
  Dim X As Long, LastRowData As Long, LastRowSites As Long
  Dim WSdata As Worksheet, WSsites As Worksheet, WSout As Worksheet
  Set WSdata = Worksheets("Sheet1")
  Set WSsites = Worksheets("Sheet2")
  Set WSout = Worksheets("sheet3")
  WSout.UsedRange.Clear
  LastRowData = WSdata.Cells(Rows.Count, "A").End(xlUp).Row
  LastRowSites = WSsites.Cells(Rows.Count, "A").End(xlUp).Row
  WSdata.Range("A1:A" & LastRowData).Copy WSout.Range("A1")
  For X = 1 To LastRowSites
    WSout.Cells(X, "B").Value = WSsites.Cells(X, "A").Value
    WSout.Columns("A").Replace WSsites.Cells(X, "B").Value, "|" & WSsites.Cells(X, "B"), xlPart
  Next
  For X = 1 To LastRowSites
    WSout.Cells(X, "C").Value = WorksheetFunction.CountIf(WSout.Columns("A"), "*||" & WSout.Cells(X, "B") & "|*")
  Next
End Sub

Rick Rothstein · Jan 12, 2014

Rick Rothstein said:

Give this macro a try...

Code:

Sub DoubleUpVerticalBarsAndCountSites()
  Dim X As Long, LastRowData As Long, LastRowSites As Long
  Dim WSdata As Worksheet, WSsites As Worksheet, WSout As Worksheet
  Set WSdata = Worksheets("Sheet1")
  Set WSsites = Worksheets("Sheet2")
  Set WSout = Worksheets("sheet3")
  WSout.UsedRange.Clear
  LastRowData = WSdata.Cells(Rows.Count, "A").End(xlUp).Row
  LastRowSites = WSsites.Cells(Rows.Count, "A").End(xlUp).Row
  WSdata.Range("A1:A" & LastRowData).Copy WSout.Range("A1")
  For X = 1 To LastRowSites
    WSout.Cells(X, "B").Value = WSsites.Cells(X, "A").Value
    WSout.Columns("A").Replace WSsites.Cells(X, "B").Value, "|" & WSsites.Cells(X, "B"), xlPart
  Next
  For X = 1 To LastRowSites
    WSout.Cells(X, "C").Value = WorksheetFunction.CountIf(WSout.Columns("A"), "*||" & WSout.Cells(X, "B") & "|*")
  Next
End Sub

I wanted to add the following to my above message, but this forum has a time limit on being able to edit a submitted message and my time for doing it had run out. Here is what I wanted to include with the above...

Just pointing out that the method you used to create the output list in your original message was flawed. It changed this line..

1235|EFGH2|STU1|ABCDEF2|GHUIJK11|BC1|gmail.com|12346|JCD@yahoo.com

to this one...

1235|EFGH2|STU1|ABCDEF2|GHUIJK11|BC1||g|mail.com|12346|JCD@|yahoo.com

Note the vertical bar between the "g" and "mail.com".... that came from replacing "mail.com" after replacing "gmail.com". My code does not do this. In order to have avoided the problem when you created your list, you would have needed to protect against this string within string problem by concatenating the leading and trailing vertical bar in order to make each item in your site list unique from each other.

rohansnaik · Jan 17, 2014

Thanks buddy It Work fine for me

Output Sheet				Site	Duplicate Count
1234\|ABCD1\|XYZ1\|ABCDEF1\|GHUIJK1\|AB1\|\|yahoo.com\|12345\|ABC@\|yahoo.com		yahoo.com	1295
1235\|EFGH2\|STU1\|ABCDEF2\|GHUIJK11\|BC1\|\|g\|mail.com\|12346\|JCD@\|yahoo.com	gmail.com	87
1236\|ABCD2\|XYZ2\|ABCDEF3\|GHUIJK21\|DC1\|\|mail.com\|12347\|ABC@\|yahoo.com		mail.com	174
1237\|EFGH3\|STU2\|ABCDEF4\|GHUIJK31\|JC1\|indiatimes.com\|12348\|JCD@\|yahoo.com	indiatimes.com	0
1238\|ABCD3\|XYZ2\|ABCDEF5\|GHUIJK41\|AB2\|timesofindia.com\|12349\|ABC@\|yahoo.com	timesofindia.com	1295
1239\|EFGH4\|STU2\|ABCDEF6\|GHUIJK51\|BC2\|wechat.com\|12350\|JCD@\|yahoo.com	wechat.com	87
1240\|ABCD4\|XYZ3\|ABCDEF7\|GHUIJK61\|DC2\|radiomirchi.com\|12351\|ABC@\|yahoo.com	radiomirchi.com	174
1241\|EFGH5\|STU3\|ABCDEF8\|GHUIJK71\|JC2\|clickonce.com\|12352\|JCD@\|yahoo.com	clickonce.com	0
1242\|ABCD5\|XYZ3\|ABCDEF9\|GHUIJK81\|AB3\|ragaa.com\|12353\|ABC@\|yahoo.com		ragaa.com	1295
1243\|EFGH6\|STU3\|ABCDEF10\|GHUIJK91\|BC3\|slice.com\|12354\|JCD@\|yahoo.com		slice.com	87
1244\|ABCD6\|XYZ4\|ABCDEF11\|GHUIJK101\|DC3\|fanta.com\|12355\|ABC@\|yahoo.com	fanta.com	174
1245\|EFGH7\|STU4\|ABCDEF12\|GHUIJK111\|JC3\|coke.com\|12356\|JCD@\|yahoo.com		coke.com	0
1246\|ABCD7\|XYZ4\|ABCDEF13\|GHUIJK121\|AB4\|thumpsup.com\|12357\|ABC@\|yahoo.com	thumpsup.com	174
1247\|EFGH8\|STU4\|ABCDEF14\|GHUIJK131\|BC4\|lyrics.com\|12358\|JCD@\|yahoo.com	lyrics.com	0
1248\|ABCD2\|XYZ2\|ABCDEF15\|GHUIJK141\|AB2\|\|yahoo.com\|12359\|ABC@\|yahoo.com
1249\|EFGH3\|STU2\|ABCDEF16\|GHUIJK151\|BC2\|\|g\|mail.com\|12360\|JCD@\|yahoo.com
1250\|ABCD3\|XYZ3\|ABCDEF17\|GHUIJK161\|DC2\|\|mail.com\|12361\|ABC@\|yahoo.com
1251\|EFGH4\|STU3\|ABCDEF18\|GHUIJK171\|JC2\|indiatimes.com\|12362\|JCD@\|yahoo.com
1252\|ABCD4\|XYZ3\|ABCDEF19\|GHUIJK181\|AB3\|timesofindia.com\|12363\|ABC@\|yahoo.com
1253\|EFGH5\|STU3\|ABCDEF20\|GHUIJK191\|BC3\|wechat.com\|12364\|JCD@\|yahoo.com
1254\|ABCD5\|XYZ4\|ABCDEF21\|GHUIJK201\|DC3\|radiomirchi.com\|12365\|ABC@\|yahoo.com
1255\|EFGH6\|STU4\|ABCDEF22\|GHUIJK211\|JC3\|clickonce.com\|12366\|JCD@\|yahoo.com
1256\|ABCD6\|XYZ4\|ABCDEF23\|GHUIJK221\|AB4\|ragaa.com\|12367\|ABC@\|yahoo.com
1257\|EFGH7\|STU4\|ABCDEF24\|GHUIJK231\|BC4\|slice.com\|12368\|JCD@\|yahoo.com
1258\|ABCD7\|XYZ5\|ABCDEF25\|GHUIJK241\|DC4\|fanta.com\|12369\|ABC@\|yahoo.com
1259\|EFGH8\|STU5\|ABCDEF26\|GHUIJK251\|JC4\|coke.com\|12370\|JCD@\|yahoo.com
1260\|ABCD8\|XYZ5\|ABCDEF27\|GHUIJK261\|AB5\|thumpsup.com\|12371\|ABC@\|yahoo.com
1261\|EFGH9\|STU5\|ABCDEF28\|GHUIJK271\|BC5\|lyrics.com\|12372\|JCD@\|yahoo.com
1262\|ABCD3\|XYZ3\|ABCDEF29\|GHUIJK281\|AB3\|\|yahoo.com\|12373\|ABC@\|yahoo.com
1263\|EFGH4\|STU3\|ABCDEF30\|GHUIJK291\|BC3\|\|g\|mail.com\|12374\|JCD@\|yahoo.com
1264\|ABCD4\|XYZ4\|ABCDEF31\|GHUIJK301\|DC3\|\|mail.com\|12375\|ABC@\|yahoo.com
1265\|EFGH5\|STU4\|ABCDEF32\|GHUIJK311\|JC3\|indiatimes.com\|12376\|JCD@\|yahoo.com
1266\|ABCD5\|XYZ4\|ABCDEF33\|GHUIJK321\|AB4\|timesofindia.com\|12377\|ABC@\|yahoo.com
1267\|EFGH6\|STU4\|ABCDEF34\|GHUIJK331\|BC4\|wechat.com\|12378\|JCD@\|yahoo.com
1268\|ABCD6\|XYZ5\|ABCDEF35\|GHUIJK341\|DC4\|radiomirchi.com\|12379\|ABC@\|yahoo.com
1269\|EFGH7\|STU5\|ABCDEF36\|GHUIJK351\|JC4\|clickonce.com\|12380\|JCD@\|yahoo.com
1270\|ABCD7\|XYZ5\|ABCDEF37\|GHUIJK361\|AB5\|ragaa.com\|12381\|ABC@\|yahoo.com
1271\|EFGH8\|STU5\|ABCDEF38\|GHUIJK371\|BC5\|slice.com\|12382\|JCD@\|yahoo.com
1272\|ABCD8\|XYZ6\|ABCDEF39\|GHUIJK381\|DC5\|fanta.com\|12383\|ABC@\|yahoo.com
1273\|EFGH9\|STU6\|ABCDEF40\|GHUIJK391\|JC5\|coke.com\|12384\|JCD@\|yahoo.com
1274\|ABCD9\|XYZ6\|ABCDEF41\|GHUIJK401\|AB6\|thumpsup.com\|12385\|ABC@\|yahoo.com
1275\|EFGH10\|STU6\|ABCDEF42\|GHUIJK411\|BC6\|lyrics.com\|12386\|JCD@\|yahoo.com
1276\|ABCD4\|XYZ4\|ABCDEF43\|GHUIJK421\|AB4\|\|yahoo.com\|12387\|ABC@\|yahoo.com
1277\|EFGH5\|STU4\|ABCDEF44\|GHUIJK431\|BC4\|\|g\|mail.com\|12388\|JCD@\|yahoo.com
1278\|ABCD5\|XYZ5\|ABCDEF45\|GHUIJK441\|DC4\|\|mail.com\|12389\|ABC@\|yahoo.com
1279\|EFGH6\|STU5\|ABCDEF46\|GHUIJK451\|JC4\|indiatimes.com\|12390\|JCD@\|yahoo.com
1280\|ABCD6\|XYZ5\|ABCDEF47\|GHUIJK461\|AB5\|timesofindia.com\|12391\|ABC@\|yahoo.com
1281\|EFGH7\|STU5\|ABCDEF48\|GHUIJK471\|BC5\|wechat.com\|12392\|JCD@\|yahoo.com
1282\|ABCD7\|XYZ6\|ABCDEF49\|GHUIJK481\|DC5\|radiomirchi.com\|12393\|ABC@\|yahoo.com
1283\|EFGH8\|STU6\|ABCDEF50\|GHUIJK491\|JC5\|clickonce.com\|12394\|JCD@\|yahoo.com
1284\|ABCD8\|XYZ6\|ABCDEF51\|GHUIJK501\|AB6\|ragaa.com\|12395\|ABC@\|yahoo.com
1285\|EFGH9\|STU6\|ABCDEF52\|GHUIJK511\|BC6\|slice.com\|12396\|JCD@\|yahoo.com
1286\|ABCD9\|XYZ7\|ABCDEF53\|GHUIJK521\|DC6\|fanta.com\|12397\|ABC@\|yahoo.com
1287\|EFGH10\|STU7\|ABCDEF54\|GHUIJK531\|JC6\|coke.com\|12398\|JCD@\|yahoo.com
1288\|ABCD10\|XYZ7\|ABCDEF55\|GHUIJK541\|AB7\|thumpsup.com\|12399\|ABC@\|yahoo.com
1289\|EFGH11\|STU7\|ABCDEF56\|GHUIJK551\|BC7\|lyrics.com\|12400\|JCD@\|yahoo.com
1290\|ABCD5\|XYZ5\|ABCDEF57\|GHUIJK561\|AB5\|\|yahoo.com\|12401\|ABC@\|yahoo.com
1291\|EFGH6\|STU5\|ABCDEF58\|GHUIJK571\|BC5\|\|g\|mail.com\|12402\|JCD@\|yahoo.com
1292\|ABCD6\|XYZ6\|ABCDEF59\|GHUIJK581\|DC5\|\|mail.com\|12403\|ABC@\|yahoo.com
1293\|EFGH7\|STU6\|ABCDEF60\|GHUIJK591\|JC5\|indiatimes.com\|12404\|JCD@\|yahoo.com
1294\|ABCD7\|XYZ6\|ABCDEF61\|GHUIJK601\|AB6\|timesofindia.com\|12405\|ABC@\|yahoo.com
1295\|EFGH8\|STU6\|ABCDEF62\|GHUIJK611\|BC6\|wechat.com\|12406\|JCD@\|yahoo.com
1296\|ABCD8\|XYZ7\|ABCDEF63\|GHUIJK621\|DC6\|radiomirchi.com\|12407\|ABC@\|yahoo.com
1297\|EFGH9\|STU7\|ABCDEF64\|GHUIJK631\|JC6\|clickonce.com\|12408\|JCD@\|yahoo.com
1298\|ABCD9\|XYZ7\|ABCDEF65\|GHUIJK641\|AB7\|ragaa.com\|12409\|ABC@\|yahoo.com
1299\|EFGH10\|STU7\|ABCDEF66\|GHUIJK651\|BC7\|slice.com\|12410\|JCD@\|yahoo.com
1300\|ABCD10\|XYZ8\|ABCDEF67\|GHUIJK661\|DC7\|fanta.com\|12411\|ABC@\|yahoo.com
1301\|EFGH11\|STU8\|ABCDEF68\|GHUIJK671\|JC7\|coke.com\|12412\|JCD@\|yahoo.com
1302\|ABCD11\|XYZ8\|ABCDEF69\|GHUIJK681\|AB8\|thumpsup.com\|12413\|ABC@\|yahoo.com

Multiple find replace with count of replacement made

rohansnaik

New Member

Excel Facts

Rick Rothstein

MrExcel MVP

Rick Rothstein

MrExcel MVP

rohansnaik

New Member

Forum statistics

Share this page

Multiple find replace with count of replacement made

rohansnaik

New Member

Excel Facts

Rick Rothstein

MrExcel MVP

Rick Rothstein

MrExcel MVP

rohansnaik

New Member

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock