rohansnaik
New Member
- Joined
- Jan 12, 2014
- Messages
- 3
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
<tbody>
</tbody>
_________________________________________________________________________________________________
I have Data in Sheet 2 as
<tbody>
</tbody>
_____________________________________________________________________________________________________________________________________
I want Output in Sheet 3 as
<tbody>
</tbody>
___________________________________________________________________________________________________
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 |
<tbody>
</tbody>
_________________________________________________________________________________________________
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 |
<tbody>
</tbody>
_____________________________________________________________________________________________________________________________________
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 |
<tbody>
</tbody>