Copying data

[gary]

Column A contains MULTIPLE occurances of each label. For
each cell in column A, there is related data in the
corresponding cell in column B. For example,

Column C contains ONE occurrance of each label in column A.
For each cell in column C, there is related data in the
corresponding cell in column B.

For example:

A B C D
1 SMITH BUS SMITH CAR
2 SMITH TRUCK JONES PLUM
3 SMITH VAN ADAMS PIG
4 JONES APPLE
5 JONES ORANGE
6 ADAMS DOG
7 ADAMS CAT
8 ADAMS HORSE
9 ADAMS FROG

If the contents of the cells in column C match the
contents of the cells in column A, how can I copy the
contents of the cells in column D to the cells in column B?

Desired results:

A B
1 SMITH CAR
2 SMITH CAR
3 SMITH CAR
4 JONES PLUM
5 JONES PLUM
6 ADAMS PIG
7 ADAMS PIG
8 ADAMS PIG
9 ADAMS PIG

gary

 

[Ken Wright]

Assuming your 2nd data table is in the range C220, then in cell B2 put the
following formula:-

=VLOOKUP(A2,$C$2:$D$20,2,0)

Copy down as far as you need

 

[gary]

(Here's a portion of my real worksheet).

If the cells in column A contains the same contents as the
cells in Column G, replace '00-0000' in column C with the
contents in column H.

A B C D F G H
1 8101815-8 1993 00-0000 1.08 8101816-9 8101816-9 083-025
2 8101815-8 1993 68-5305 3.54 8101817-0 8101817-0 083-025
3 8101815-8 1993 68-5402 9.54 8101819-2 8101819-2 083-025
4 8101815-8 1994 00-0000 2.70 8102247-8 8102247-8 099-069
5 8101815-8 1994 68-5305 3.56 8102251-1 8102251-1 099-069
6 8101815-8 1994 68-5402 9.54 8102268-7 8102268-7 005-018
7 8101815-8 1995 00-0000 3.68 8102297-3 8102297-3 099-116
8 8101815-8 1995 68-5305 3.56 8102327-7 8102327-7 083-027
9 8101815-8 1995 68-5402 9.54 8102442-7 8102442-7 058-054
10 8101815-8 1996 00-0000 4.62 8344562-0 8344562-0 005-043
11 8101815-8 1996 68-5305 3.56 8346528-6 8346528-6 024-249
12 8101815-8 1996 68-5402 9.54 8347368-1 8347368-1 024-030
13 8101815-8 1997 00-0000 6.30 8347370-2 8347370-2 024-153
14 8101815-8 1997 68-5305 3.56 8347373-5 8347373-5 024-030
15 8101815-8 1997 68-5402 9.54 8347376-8 8347376-8 024-153
16 8101816-9 1993 00-0000 9.18 8348241-2 8348241-2 008-109
17 8101816-9 1993 68-5305 6.78 8349820-8 8349820-8 009-000
18 8101816-9 1993 68-5402 9.78 8349821-9 8349821-9 009-000
19 8101816-9 1994 00-0000 9.96 8350453-4 8350453-4 098-038
20 8101816-9 1994 68-5305 6.78 8350455-6 8350455-6 098-072
21 8101816-9 1994 68-5402 9.76 8350566-3 8350566-3 071-024
22 8101816-9 1995 00-0000 .44 8350865-3 8350865-3 018-284
23 8101816-9 1995 68-5305 6.78 8352754-2 8352754-2 009-002
24 8101816-9 1995 68-5402 9.78 8352801-1 8352801-1 099-100
25 8101816-9 1996 00-0000 .88 8354320-9 8354320-9 061-032
26 8101816-9 1996 68-5305 6.78 8354321-0 8354321-0 061-032
27 8101816-9 1996 68-5402 9.76 8354381-4 8354381-4 087-014
28 8101816-9 1997 00-0000 1.70 8354382-5 8354382-5 087-014
29 8101816-9 1997 68-5305 6.78 8354583-0 8354583-0 065-053
30 8101816-9 1997 68-5402 9.76 8354584-1 8354584-1 065-053
31 8101817-0 1993 00-0000 2.54 8354634-3 8354634-3 065-134
32 8101817-0 1993 68-5305 6.78 8354635-4 8354635-4 065-134
33 8101817-0 1993 68-5402 9.76 8355624-7 8355624-7 009-000
34 8101817-0 1994 00-0000 3.40 8356171-7 8356171-7 024-040
35 8101817-0 1994 68-5305 6.78 8356294-5 8356294-5 059-116
36 8101817-0 1994 68-5402 9.76 8356420-2 8356420-2 007-067
37 8101817-0 1995 00-0000 3.92 8357159-0 8357159-0 003-000
38 8101817-0 1995 68-5305 6.78 8357160-0 8357160-0 003-000
39 8101817-0 1995 68-5402 9.76 8357219-1 8357219-1 087-014
40 8101817-0 1996 00-0000 4.40 8357220-1 8357220-1 087-014
41 8101817-0 1996 68-5305 6.78 8357221-2 8357221-2 087-014
42 8101817-0 1996 68-5402 9.76 8358390-4 8358390-4 054-111
43 8101817-0 1997 00-0000 5.30 8358716-6 8358716-6 054-111
44 8101817-0 1997 68-5305 6.78 8359518-7 8359518-7 056-004
45 8101817-0 1997 68-5402 9.76 8359519-8 8359519-8 056-004
46 8101819-2 1992 00-0000 8.46 8359520-8 8359520-8 056-004
47 8101819-2 1992 68-1852 4.40 8359522-0 8359522-0 056-004
48 8101819-2 1992 68-5305 2.64 8359523-1 8359523-1 056-004
49 8101819-2 1992 68-5402 5.28 8359524-2 8359524-2 056-004
50 8101819-2 1993 00-0000 9.02 8359525-3 8359525-3 056-004
51 8101819-2 1993 68-5305 3.66 8359526-4 8359526-4 056-004
52 8101819-2 1993 68-5402 5.28 8359527-5 8359527-5 056-004
53 8101819-2 1994 00-0000 9.60 8359528-6 8359528-6 056-004
54 8101819-2 1994 68-5305 3.66 8359529-7 8359529-7 056-004
55 8101819-2 1994 68-5402 5.28 8359530-7 8359530-7 056-004
56 8101819-2 1995 00-0000 9.94 8359531-8 8359531-8 056-004
57 8101819-2 1995 68-5305 3.66 8359532-9 8359532-9 056-004
58 8101819-2 1995 68-5402 5.28 8359533-0 8359533-0 056-004
59 8101819-2 1996 00-0000 .28 8359534-1 8359534-1 056-004
60 8101819-2 1996 68-5305 3.66 8359535-2 8359535-2 056-004
61 8101819-2 1996 68-5402 5.28 8359536-3 8359536-3 055-008

 

[Ken Wright]

OK, you cheated and didn't give us the full spec first time round <g>

Is it just the 00-00000 bits you want to replace with whatever the corresponding
data is in H if A matches G. What about the other values in Col C other than
the 00-000000, do they stay as is??

 

[Ken Wright]

And just in case you hit reply, I'll snip the note here, as I should have done
before I replied with my last note :-(

 

[gary]

Ken,

Only the '00-0000' contents get replaced; the non-'00-
0000' contents stay as is.

(Remember, the data in Column A occurs MULTIPLE times; the
data in column G occurs only ONCE).

=======

I provided only the data pertaining to my question because
the "message" area of my message is all the room
available; attachments aren't allowed due to possible
viruses.

By supplying one some of the data the experts' suggestions
cover only that data. However, if my worksheet contains
other data (i.e., in other columns, in this case), I then
need to change the experts' suggestions to fit reality.
But this doesn't always work.

gary

 

[Ken Wright]

OK - Back up the file first, and then you will need a helper column initially in
say Col J

Assumptions:-

Your data in Cols G & H ranges from row 1 to 100
Your data in Cols A:F ranges from row 1 to 1000

Every cell that needs changing in Col C is the exact same value, ie 00-0000
Everywhere this value appears you want the value from Col H where Col G matches
what is in A
There are no missing values, and for every instance of 00-0000 in Col C, the
value in Col A is to be found in Col G.

Headers are all in row 1

In J2 put the following formula:-

=IF(C2<>"00-0000",C2,VLOOKUP(A2,$G$1:$H$100,2,0))

Copy down to J1000

Select J1:J1000 and copy
Select C1 and paste special as values

Delete Col J