Vba to lookup for specif alphanumeric value in a column and pull value from adjacent cell

[saravanan1981]

I have a Data in Sheet "Offline"
in Column "D" I have Numbers and Alpha Numeric Numbers.(Heading "UTR")
Some cells Single Might contains single numeric or alphanumeric number
Some cells Single Might contains multiple Numeric and Alpha Numeric Numbers separated with"/"

In sheet "UTR"
I have list of only single numeric or alphanumeric number in column "I" and have some amount against that in Column "J"
Some cells in Column "I" Might contain "#N/A" or "N/A" or empty. Please ignore of it has any these three.

Task:
Need VBA to perform following task. Please find attached.
1. On sheet offline, from Column "D" split/extract the contents by "/" (just like text to column). The extraction should start from column G onwards.
2. Then check for maximum number of split from single cell.
3. Give Heading to all splited column as (UTR-1, UTR-2, UTR-3 etc. depend upon the number of splits). For example: if D2 contains 2 UTR and D3 contains 7 UTR then D3 should be splited to 7 columns. i.e. Column G to Column M and name G1 as UTR-1 , H1 as UTR-2, I1 as UTR-1 and so on.
4. Insert One column after every extracted UTR column and Name it as AMT-1, AMT-2, AMT-3 etc.
5. Now I want to lookup for UTR No. from sheet “Offline” to column “I” of sheet “UTR”. IF UTR exists in column “I” then pull the amount against the UTR and save it against the matching UTR in Column “UTR-1”. In same way I wants to do it for all columns.
6. After that I wants to Insert two Column at the end “TOTAL Number of UTR “ and “TOTAL AMT” . In “TOTAL Number of UTR” I want the count of total UTR extracted from Column G onwards and in column “TOTAL AMT” I want the sum of all amount pulled against all UTR. In sheet off line I have marked desired output in green colour and result in yellow colour.

Please note

1. In column D of sheet “Offline: the separator “/” of UTR can be any number. Hence have insert UTR-1, UTR-2 etc till it reached the last separation.

2. The Code should run in sheet “offline” for those records till the end of Sr. No. (Column A)