View Full Version : Solved: Copy cell to multiple rows
Meatball
12-30-2009, 02:21 PM
I have a list of tool sets. The qty/rows for each set varies. In col A I have the set part number and then the individual part numbers. In col B I have descriptions. In col C I have the set part number for all the rows of that set.
Col A--- Col B------ Col C
1234--- wrench set- 1234
1034--- 1/2-------- 1234
1035--- 9/16------- 1234
1036--- 3/4-------- 1234
I want to add the set(1234) description from Col B to each row of the set in Col F.
Col A--- Col B------- Col C--- Col F
1234--- wrench set-- 1234--- wrench set
1034--- 1/2---------- 1234--- wrench set
1035--- 9/16--------- 1234--- wrench set
1036--- 3/4---------- 1234--- wrench set
I am not good with using Match and was hoping someone might be able to help me with this. The spreadsheet is 16000 lines long so I would hate to do this manually. Thanks in advance for any help.
mikerickson
12-30-2009, 03:56 PM
If you put =VLOOKUP(C2, A:B, 2, FALSE) in F2 and drag down, it looks like that might do what you want.
Meatball
01-04-2010, 07:00 AM
Thanks for the reply, unfortunatly that would also be extremely time consuming. As I said there are 16,000 lines worth of varying sizes of sets so I would I have to adjust the line it was looking at for each set. I am looking for something that would look at column C and pick up from Col B at the first instance of each number in Col C. Maybe a longer example would be better;
Col A--- Col B------ Col C
1234--- wrench set- 1234
1034--- 1/2-------- 1234
1035--- 9/16------- 1234
1036--- 3/4-------- 1234
1111----socket set-1111
1101----small------1111
1102----med-------1111
1103----large------1111
1104----xlarge-----1111
2222----wrench set-2222
2210----1/8--------2222
2212----1/4--------2222
result wanted;
Col A--- Col B------- Col C--- Col F
1234--- wrench set-- 1234--- wrench set
1034--- 1/2---------- 1234--- wrench set
1035--- 9/16--------- 1234--- wrench set
1036--- 3/4---------- 1234--- wrench set
1111----socket set-1111-----socket set
1101----small------1111-----socket set
1102----med-------1111----socket set
1103----large------1111-----socket set
1104----xlarge-----1111-----socket set
2222----wrench set-2222----wrench set
2210----1/8--------2222----wrench set
2212----1/4--------2222----wrench set
mikerickson
01-04-2010, 07:31 AM
I'm trying to find the logic in the part numbering system that says that 1035 is a sub-part to 1234, but not to 1034. And 1111 is a set that has sub-parts.
Since I read and understand the meaning of column B, it makes sense, but computers don't understand meaning.
I could use the rule "if a part name does not contain "set", then that part is a sub-part of the preceding part-name that does contain "set" ", but I see problems if, later on, there is a section
...
wrench set
1/8
1/4
extention
hammer
phillips screwdriver
That logic would say that the hammer was a sub-part of the wrench set.
Is there some logic to set/sub-part numbering that I haven't found?
mbarron
01-04-2010, 08:25 AM
If column C already has the Set numbers in it, the VLOOKUP will do what you are asking. There would be no need to "adjust the line it was looking at for each set"
Meatball
01-04-2010, 08:58 AM
mikerickson, I made up the part numbers as I wrote the post so if you look at the numbers as being in order then it looks goofy. In what I deal with the set part numbers are usually nothing like the part numbers that are in that set or the order is goofy. I do not always understand the system used by the tool mfg's either.
And you are both correct about the formula. I use vlookup when dealing with all unique items in a list. I forgot about it looking at the first instance if an item is repeated. mbarron, thanks for forcing me to look at it again.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.