Find and Replace Cell Values
 

I urgently need a macro or formula to find and replace the values of certain
cells.
I need to find and replace the common values in this file:
Pool Plan Market Val Ratio
GEP-100 GFI-152 103,943,033.31 0.019021
GEP-100 GMDB1 3,303,254,928.27 0.604476
GEP-100 GMDB2 1,767,492,502.30 0.323441
GEP-100 ISAAP-100 289,965,652.00 0.053062
GEP-106 GFI-152 20,944,594.89 0.006014
GEP-106 GMDB1 2,254,017,316.78 0.647171
GEP-106 GMDB2 1,186,390,384.08 0.340636
GEP-106 ISAAP-100 21,520,819.53 0.006179
GEP-108 DDB1 504,345,291.42 0.091563
GEP-108 DDB2 363,851,414.82 0.066056
GEP-108 GFI-152 22,825,117.39 0.004144
GEP-108 GMDB1 2,890,176,184.53 0.524706
GEP-108 GMDB2 1,644,197,834.06 0.298500
GEP-108 ISAAP-100 82,796,326.53 0.015031
GEP-109 GEP-100 2,134,768,665.60 1.000000
GEP-110 GFI-152 24,221,922.33 0.041704
GEP-110 GMDB1 392,702,862.41 0.676140
GEP-110 GMDB2 128,730,237.94 0.221643
GEP-110 ISAAP-100 35,146,050.27 0.060513
GEP-112 GFI-152 17,476,993.07 0.051321
GEP-112 GMDB1 207,131,221.36 0.608240
GEP-112 GMDB2 115,933,721.62 0.340439
GEP-113 GMDB1 971,384,789.85 0.630864
GEP-113 GMDB2 568,383,970.65 0.369136
GEP-114 GEP-110 205,534,317.56 0.943823
GEP-114 SW01 12,233,583.74 0.056177
Replacing the values with these values.

POOL PLAN ID MV RATIO
GEP-106 GFI-152 20918897.59 0.00628336
GEP-106 GMDB1 2155512837 0.647446331
GEP-106 GMDB2 1131403478 0.339837007
GEP-106 ISAAP-100 21418091.37 0.006433302
GEP-108 GFI-152 22618548.15 0.004189709
GEP-108 GMDB1 2804562851 0.519498525
GEP-108 GMDB2 1629317705 0.301803949
GEP-108 DDB2 360558528.8 0.066787458
GEP-108 DDB1 499780924 0.092576086
GEP-108 ISAAP-100 81757810.57 0.015144272
GEP-109 GEP-100 2179141698 1
GEP-110 GFI-152 24777268.4 0.042221778
GEP-110 GMDB1 394546032.1 0.672327337
GEP-110 GMDB2 131681689.5 0.224392574
GEP-110 ISAAP-100 35831228.39 0.061058311
GFI-171 GMDB1 1228815863 1
GEP-114 GEP-110 207380802.2 0.943321542
GEP-114 SW01 12460252.01 0.056678458
GEP-113 GMDB1 959112444.6 0.627232676
GEP-113 GMDB2 570005028.7 0.372767324
GEP-100 GFI-152 44792935.33 0.008235711
GEP-100 GMDB1 3297827980 0.60634466
GEP-100 GMDB2 1801677567 0.33125972
GEP-100 ISAAP-100 294568546.4 0.05415991
GFIP-101 GMDB1 7854211838 0.780451808
GFIP-101 GMDB2 1974182455 0.196169176
GFIP-101 ISAAP-100 235278775 0.023379016
GFIP-102 GFI-152 47357599.15 0.010828328
GFIP-102 GMDB1 2678903365 0.612531985
GFIP-102 GMDB2 1492724526 0.34131187
GFIP-102 DDB2 49911992.56 0.011412391
GFIP-102 DDB1 66156478.36 0.015126697
GFIP-102 ISAAP-100 38437432.79 0.008788729

Find and Replace Cell Values
 

Not clear what you have done in going from dataset 1 to dataset 2
Are you summarizing - consolidating?
So it two rows have the same Pool and Plan then you add the Values and
ratios?
best wishes

"Jeff" wrote in message
...
I urgently need a macro or formula to find and replace the values of
certain
cells.
I need to find and replace the common values in this file:
Pool Plan Market Val Ratio
GEP-100 GFI-152 103,943,033.31 0.019021
GEP-100 GMDB1 3,303,254,928.27 0.604476
GEP-100 GMDB2 1,767,492,502.30 0.323441
GEP-100 ISAAP-100 289,965,652.00 0.053062
GEP-106 GFI-152 20,944,594.89 0.006014
GEP-106 GMDB1 2,254,017,316.78 0.647171
GEP-106 GMDB2 1,186,390,384.08 0.340636
GEP-106 ISAAP-100 21,520,819.53 0.006179
GEP-108 DDB1 504,345,291.42 0.091563
GEP-108 DDB2 363,851,414.82 0.066056
GEP-108 GFI-152 22,825,117.39 0.004144
GEP-108 GMDB1 2,890,176,184.53 0.524706
GEP-108 GMDB2 1,644,197,834.06 0.298500
GEP-108 ISAAP-100 82,796,326.53 0.015031
GEP-109 GEP-100 2,134,768,665.60 1.000000
GEP-110 GFI-152 24,221,922.33 0.041704
GEP-110 GMDB1 392,702,862.41 0.676140
GEP-110 GMDB2 128,730,237.94 0.221643
GEP-110 ISAAP-100 35,146,050.27 0.060513
GEP-112 GFI-152 17,476,993.07 0.051321
GEP-112 GMDB1 207,131,221.36 0.608240
GEP-112 GMDB2 115,933,721.62 0.340439
GEP-113 GMDB1 971,384,789.85 0.630864
GEP-113 GMDB2 568,383,970.65 0.369136
GEP-114 GEP-110 205,534,317.56 0.943823
GEP-114 SW01 12,233,583.74 0.056177
Replacing the values with these values.

POOL PLAN ID MV RATIO
GEP-106 GFI-152 20918897.59 0.00628336
GEP-106 GMDB1 2155512837 0.647446331
GEP-106 GMDB2 1131403478 0.339837007
GEP-106 ISAAP-100 21418091.37 0.006433302
GEP-108 GFI-152 22618548.15 0.004189709
GEP-108 GMDB1 2804562851 0.519498525
GEP-108 GMDB2 1629317705 0.301803949
GEP-108 DDB2 360558528.8 0.066787458
GEP-108 DDB1 499780924 0.092576086
GEP-108 ISAAP-100 81757810.57 0.015144272
GEP-109 GEP-100 2179141698 1
GEP-110 GFI-152 24777268.4 0.042221778
GEP-110 GMDB1 394546032.1 0.672327337
GEP-110 GMDB2 131681689.5 0.224392574
GEP-110 ISAAP-100 35831228.39 0.061058311
GFI-171 GMDB1 1228815863 1
GEP-114 GEP-110 207380802.2 0.943321542
GEP-114 SW01 12460252.01 0.056678458
GEP-113 GMDB1 959112444.6 0.627232676
GEP-113 GMDB2 570005028.7 0.372767324
GEP-100 GFI-152 44792935.33 0.008235711
GEP-100 GMDB1 3297827980 0.60634466
GEP-100 GMDB2 1801677567 0.33125972
GEP-100 ISAAP-100 294568546.4 0.05415991
GFIP-101 GMDB1 7854211838 0.780451808
GFIP-101 GMDB2 1974182455 0.196169176
GFIP-101 ISAAP-100 235278775 0.023379016
GFIP-102 GFI-152 47357599.15 0.010828328
GFIP-102 GMDB1 2678903365 0.612531985
GFIP-102 GMDB2 1492724526 0.34131187
GFIP-102 DDB2 49911992.56 0.011412391
GFIP-102 DDB1 66156478.36 0.015126697
GFIP-102 ISAAP-100 38437432.79 0.008788729

Find and Replace Cell Values
 

I want to replace the value in dataset 1 using the values of dataset 2 for
all common between the two set of data.

"Bernard Liengme" wrote:

Not clear what you have done in going from dataset 1 to dataset 2
Are you summarizing - consolidating?
So it two rows have the same Pool and Plan then you add the Values and
ratios?
best wishes

"Jeff" wrote in message
...
I urgently need a macro or formula to find and replace the values of
certain
cells.
I need to find and replace the common values in this file:
Pool Plan Market Val Ratio
GEP-100 GFI-152 103,943,033.31 0.019021
GEP-100 GMDB1 3,303,254,928.27 0.604476
GEP-100 GMDB2 1,767,492,502.30 0.323441
GEP-100 ISAAP-100 289,965,652.00 0.053062
GEP-106 GFI-152 20,944,594.89 0.006014
GEP-106 GMDB1 2,254,017,316.78 0.647171
GEP-106 GMDB2 1,186,390,384.08 0.340636
GEP-106 ISAAP-100 21,520,819.53 0.006179
GEP-108 DDB1 504,345,291.42 0.091563
GEP-108 DDB2 363,851,414.82 0.066056
GEP-108 GFI-152 22,825,117.39 0.004144
GEP-108 GMDB1 2,890,176,184.53 0.524706
GEP-108 GMDB2 1,644,197,834.06 0.298500
GEP-108 ISAAP-100 82,796,326.53 0.015031
GEP-109 GEP-100 2,134,768,665.60 1.000000
GEP-110 GFI-152 24,221,922.33 0.041704
GEP-110 GMDB1 392,702,862.41 0.676140
GEP-110 GMDB2 128,730,237.94 0.221643
GEP-110 ISAAP-100 35,146,050.27 0.060513
GEP-112 GFI-152 17,476,993.07 0.051321
GEP-112 GMDB1 207,131,221.36 0.608240
GEP-112 GMDB2 115,933,721.62 0.340439
GEP-113 GMDB1 971,384,789.85 0.630864
GEP-113 GMDB2 568,383,970.65 0.369136
GEP-114 GEP-110 205,534,317.56 0.943823
GEP-114 SW01 12,233,583.74 0.056177
Replacing the values with these values.

POOL PLAN ID MV RATIO
GEP-106 GFI-152 20918897.59 0.00628336
GEP-106 GMDB1 2155512837 0.647446331
GEP-106 GMDB2 1131403478 0.339837007
GEP-106 ISAAP-100 21418091.37 0.006433302
GEP-108 GFI-152 22618548.15 0.004189709
GEP-108 GMDB1 2804562851 0.519498525
GEP-108 GMDB2 1629317705 0.301803949
GEP-108 DDB2 360558528.8 0.066787458
GEP-108 DDB1 499780924 0.092576086
GEP-108 ISAAP-100 81757810.57 0.015144272
GEP-109 GEP-100 2179141698 1
GEP-110 GFI-152 24777268.4 0.042221778
GEP-110 GMDB1 394546032.1 0.672327337
GEP-110 GMDB2 131681689.5 0.224392574
GEP-110 ISAAP-100 35831228.39 0.061058311
GFI-171 GMDB1 1228815863 1
GEP-114 GEP-110 207380802.2 0.943321542
GEP-114 SW01 12460252.01 0.056678458
GEP-113 GMDB1 959112444.6 0.627232676
GEP-113 GMDB2 570005028.7 0.372767324
GEP-100 GFI-152 44792935.33 0.008235711
GEP-100 GMDB1 3297827980 0.60634466
GEP-100 GMDB2 1801677567 0.33125972
GEP-100 ISAAP-100 294568546.4 0.05415991
GFIP-101 GMDB1 7854211838 0.780451808
GFIP-101 GMDB2 1974182455 0.196169176
GFIP-101 ISAAP-100 235278775 0.023379016
GFIP-102 GFI-152 47357599.15 0.010828328
GFIP-102 GMDB1 2678903365 0.612531985
GFIP-102 GMDB2 1492724526 0.34131187
GFIP-102 DDB2 49911992.56 0.011412391
GFIP-102 DDB1 66156478.36 0.015126697
GFIP-102 ISAAP-100 38437432.79 0.008788729


.

Find and Replace Cell Values
 

This subroutine copies the dataset on Sheeet1 to Sheet3, replacing columns 3
and 4 with data from the dataset on SHeet2 when the first two columns match

Sub tryme()
Worksheets("sheet1").Activate
Set rng1 = Range(Cells(1, 1), Cells(1, 4).End(xlDown))
last1 = rng1.Count / 4
Worksheets("sheet2").Activate
Set rng2 = Range(Cells(1, 1), Cells(1, 4).End(xlDown))
last2 = rng2.Count / 4
Worksheets("Sheet3").Activate
Cells(1, 1) = rng1(1, 1)
Cells(1, 2) = rng1(1, 2)
Cells(1, 3) = rng1(1, 3)
Cells(1, 4) = rng1(1, 4)
For j = 2 To last1
Cells(j, 1) = rng1(j, 1)
Cells(j, 2) = rng1(j, 2)
Cells(j, 3) = rng1(j, 3)
Cells(j, 4) = rng1(j, 4)
For k = 1 To last2
If rng1(j, 1) = rng2(j, 1) And rng2(k, 2) = rng2(k, 2) Then
Cells(j, 3) = rng2(k, 3)
Cells(j, 4) = rng2(k, 4)
End If
Next k
Next j
End Sub

The result is
POOL PLAN MV RATIO
GEP-100 GFI-152 103,943,033.31 0.019021
GEP-100 GMDB1 3,303,254,928.27 0.604476
GEP-100 GMDB2 1,767,492,502.30 0.323441
GEP-100 ISAAP-100 289,965,652.00 0.053062
GEP-106 GFI-152 20,944,594.89 0.006014
GEP-106 GMDB1 2,254,017,316.78 0.647171
GEP-106 GMDB2 1,186,390,384.08 0.340636
GEP-106 ISAAP-100 21,520,819.53 0.006179
GEP-108 DDB1 38,437,432.79 0.008788729
GEP-108 DDB2 38,437,432.79 0.008788729
GEP-108 GFI-152 22,825,117.39 0.004144
GEP-108 GMDB1 2,890,176,184.53 0.524706
GEP-108 GMDB2 1,644,197,834.06 0.2985
GEP-108 ISAAP-100 82,796,326.53 0.015031
GEP-109 GEP-100 2,134,768,665.60 1
GEP-110 GFI-152 24,221,922.33 0.041704
GEP-110 GMDB1 392,702,862.41 0.67614
GEP-110 GMDB2 128,730,237.94 0.221643
GEP-110 ISAAP-100 35,146,050.27 0.060513
GEP-112 GFI-152 17,476,993.07 0.051321
GEP-112 GMDB1 207,131,221.36 0.60824
GEP-112 GMDB2 115,933,721.62 0.340439
GEP-113 GMDB1 971,384,789.85 0.630864
GEP-113 GMDB2 568,383,970.65 0.369136
GEP-114 GEP-110 205,534,317.56 0.943823
GEP-114 SW01 12,233,583.74 0.056177

Is this what you are aiming for?
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"Jeff" wrote in message
...
I want to replace the value in dataset 1 using the values of dataset 2 for
all common between the two set of data.

"Bernard Liengme" wrote:

Not clear what you have done in going from dataset 1 to dataset 2
Are you summarizing - consolidating?
So it two rows have the same Pool and Plan then you add the Values and
ratios?
best wishes

"Jeff" wrote in message
...
I urgently need a macro or formula to find and replace the values of
certain
cells.
I need to find and replace the common values in this file:
Pool Plan Market Val Ratio
GEP-100 GFI-152 103,943,033.31 0.019021
GEP-100 GMDB1 3,303,254,928.27 0.604476
GEP-100 GMDB2 1,767,492,502.30 0.323441
GEP-100 ISAAP-100 289,965,652.00 0.053062
GEP-106 GFI-152 20,944,594.89 0.006014
GEP-106 GMDB1 2,254,017,316.78 0.647171
GEP-106 GMDB2 1,186,390,384.08 0.340636
GEP-106 ISAAP-100 21,520,819.53 0.006179
GEP-108 DDB1 504,345,291.42 0.091563
GEP-108 DDB2 363,851,414.82 0.066056
GEP-108 GFI-152 22,825,117.39 0.004144
GEP-108 GMDB1 2,890,176,184.53 0.524706
GEP-108 GMDB2 1,644,197,834.06 0.298500
GEP-108 ISAAP-100 82,796,326.53 0.015031
GEP-109 GEP-100 2,134,768,665.60 1.000000
GEP-110 GFI-152 24,221,922.33 0.041704
GEP-110 GMDB1 392,702,862.41 0.676140
GEP-110 GMDB2 128,730,237.94 0.221643
GEP-110 ISAAP-100 35,146,050.27 0.060513
GEP-112 GFI-152 17,476,993.07 0.051321
GEP-112 GMDB1 207,131,221.36 0.608240
GEP-112 GMDB2 115,933,721.62 0.340439
GEP-113 GMDB1 971,384,789.85 0.630864
GEP-113 GMDB2 568,383,970.65 0.369136
GEP-114 GEP-110 205,534,317.56 0.943823
GEP-114 SW01 12,233,583.74 0.056177
Replacing the values with these values.

POOL PLAN ID MV RATIO
GEP-106 GFI-152 20918897.59 0.00628336
GEP-106 GMDB1 2155512837 0.647446331
GEP-106 GMDB2 1131403478 0.339837007
GEP-106 ISAAP-100 21418091.37 0.006433302
GEP-108 GFI-152 22618548.15 0.004189709
GEP-108 GMDB1 2804562851 0.519498525
GEP-108 GMDB2 1629317705 0.301803949
GEP-108 DDB2 360558528.8 0.066787458
GEP-108 DDB1 499780924 0.092576086
GEP-108 ISAAP-100 81757810.57 0.015144272
GEP-109 GEP-100 2179141698 1
GEP-110 GFI-152 24777268.4 0.042221778
GEP-110 GMDB1 394546032.1 0.672327337
GEP-110 GMDB2 131681689.5 0.224392574
GEP-110 ISAAP-100 35831228.39 0.061058311
GFI-171 GMDB1 1228815863 1
GEP-114 GEP-110 207380802.2 0.943321542
GEP-114 SW01 12460252.01 0.056678458
GEP-113 GMDB1 959112444.6 0.627232676
GEP-113 GMDB2 570005028.7 0.372767324
GEP-100 GFI-152 44792935.33 0.008235711
GEP-100 GMDB1 3297827980 0.60634466
GEP-100 GMDB2 1801677567 0.33125972
GEP-100 ISAAP-100 294568546.4 0.05415991
GFIP-101 GMDB1 7854211838 0.780451808
GFIP-101 GMDB2 1974182455 0.196169176
GFIP-101 ISAAP-100 235278775 0.023379016
GFIP-102 GFI-152 47357599.15 0.010828328
GFIP-102 GMDB1 2678903365 0.612531985
GFIP-102 GMDB2 1492724526 0.34131187
GFIP-102 DDB2 49911992.56 0.011412391
GFIP-102 DDB1 66156478.36 0.015126697
GFIP-102 ISAAP-100 38437432.79 0.008788729


.