| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy and Fill to another column
I'm trying to find a way to copy the Qty from Column "C" and paste into
Column "D" next to it's respective hardware cell(s). I hope the follwoing shows what I'm trying to explain... This is what I have: A B C D Title Param Part Qty D01 Part # 10-32 | BOLT, HEX DRIVE D01 Part # 10-32 | COLLAR D01 Qty 6 D02 Part # SPECIAL | NUT, CLIP-ON D02 Qty 7 D03 Part # 1/4-20 | BOLT, HEX DRIVE D03 Part # 1/4-20 | COLLAR D03 Qty 8 D34 Part # 3/8" | BOLT D34 Part # 3/8" | WASHER, PLAIN D34 Part # 3/8" | BUSHING, PLAIN D34 Part # 3/8" | WASHER, PLAIN D34 Part # 3/8" | NUT, SELF-LOCKING D34 Qty 2 D21 Part # 10-32 | WASHER, SPRING D21 Part # 10-32 | WASHER, PLAIN D21 Part # S.S | WASHER, PLAIN D21 Part # S.S | NUT, HEX D21 Qty 1 D26 Part # 5/16 | RIVET, UNIVERSAL HD D26 Qty 8 This is what I'm trying to get to: A B C D Title Param Part Qty D01 Part # 10-32 | BOLT, HEX DRIVE 6 D01 Part # 10-32 | COLLAR 6 D01 Qty 6 D02 Part # SPECIAL | NUT, CLIP-ON 7 D02 Qty 7 D03 Part # 1/4-20 | BOLT, HEX DRIVE 8 D03 Part # 1/4-20 | COLLAR 8 D03 Qty 8 D34 Part # 3/8" | BOLT 2 D34 Part # 3/8" | WASHER, PLAIN 2 D34 Part # 3/8" | BUSHING, PLAIN 2 D34 Part # 3/8" | WASHER, PLAIN 2 D34 Part # 3/8" | NUT, SELF-LOCKING 2 D34 Qty 2 D21 Part # 10-32 | WASHER, SPRING 1 D21 Part # 10-32 | WASHER, PLAIN 1 D21 Part # S.S | WASHER, PLAIN 1 D21 Part # S.S | NUT, HEX 1 D21 Qty 1 D26 Part # 5/16 | RIVET, UNIVERSAL HD 8 D26 Qty 8 There can be as few as 1 or as many as 7 rows of hardware needed to copy quantities to. I've tried pivot tables, but have not had any luck. Does anyone know if this even possible with a macro? Thanks for any help. Tony |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Copy and Fill to another column
One way ..
Assuming posted data is in row 2 down Place this expression into D2's formula bar, then press CTRL+SHIFT+ENTER to "array-enter" & confirm the formula (instead of just pressing ENTER): =IF(B2="Qty","",INDEX($C$2:$C$200,MATCH(1,($A$2:$A $200=A2)*($B$2:$B$200="Qty"),0))) Copy D2 down as far as required. Tested here on your data as posted, it returns the indicated results that you're after, exactly. Adapt the ranges to suit the extent of your actual data. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Tony S." wrote: I'm trying to find a way to copy the Qty from Column "C" and paste into Column "D" next to it's respective hardware cell(s). I hope the follwoing shows what I'm trying to explain... This is what I have: A B C D Title Param Part Qty D01 Part # 10-32 | BOLT, HEX DRIVE D01 Part # 10-32 | COLLAR D01 Qty 6 D02 Part # SPECIAL | NUT, CLIP-ON D02 Qty 7 D03 Part # 1/4-20 | BOLT, HEX DRIVE D03 Part # 1/4-20 | COLLAR D03 Qty 8 D34 Part # 3/8" | BOLT D34 Part # 3/8" | WASHER, PLAIN D34 Part # 3/8" | BUSHING, PLAIN D34 Part # 3/8" | WASHER, PLAIN D34 Part # 3/8" | NUT, SELF-LOCKING D34 Qty 2 D21 Part # 10-32 | WASHER, SPRING D21 Part # 10-32 | WASHER, PLAIN D21 Part # S.S | WASHER, PLAIN D21 Part # S.S | NUT, HEX D21 Qty 1 D26 Part # 5/16 | RIVET, UNIVERSAL HD D26 Qty 8 This is what I'm trying to get to: A B C D Title Param Part Qty D01 Part # 10-32 | BOLT, HEX DRIVE 6 D01 Part # 10-32 | COLLAR 6 D01 Qty 6 D02 Part # SPECIAL | NUT, CLIP-ON 7 D02 Qty 7 D03 Part # 1/4-20 | BOLT, HEX DRIVE 8 D03 Part # 1/4-20 | COLLAR 8 D03 Qty 8 D34 Part # 3/8" | BOLT 2 D34 Part # 3/8" | WASHER, PLAIN 2 D34 Part # 3/8" | BUSHING, PLAIN 2 D34 Part # 3/8" | WASHER, PLAIN 2 D34 Part # 3/8" | NUT, SELF-LOCKING 2 D34 Qty 2 D21 Part # 10-32 | WASHER, SPRING 1 D21 Part # 10-32 | WASHER, PLAIN 1 D21 Part # S.S | WASHER, PLAIN 1 D21 Part # S.S | NUT, HEX 1 D21 Qty 1 D26 Part # 5/16 | RIVET, UNIVERSAL HD 8 D26 Qty 8 There can be as few as 1 or as many as 7 rows of hardware needed to copy quantities to. I've tried pivot tables, but have not had any luck. Does anyone know if this even possible with a macro? Thanks for any help. Tony |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Copy and Fill to another column
Hi Max,
That may just do the trick. The only wrinkle is I have to got to a meeting and won't be able to play until tomorrow. I'll let you know. "Max" wrote: One way .. Assuming posted data is in row 2 down Place this expression into D2's formula bar, then press CTRL+SHIFT+ENTER to "array-enter" & confirm the formula (instead of just pressing ENTER): =IF(B2="Qty","",INDEX($C$2:$C$200,MATCH(1,($A$2:$A $200=A2)*($B$2:$B$200="Qty"),0))) Copy D2 down as far as required. Tested here on your data as posted, it returns the indicated results that you're after, exactly. Adapt the ranges to suit the extent of your actual data. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Tony S." wrote: I'm trying to find a way to copy the Qty from Column "C" and paste into Column "D" next to it's respective hardware cell(s). I hope the follwoing shows what I'm trying to explain... This is what I have: A B C D Title Param Part Qty D01 Part # 10-32 | BOLT, HEX DRIVE D01 Part # 10-32 | COLLAR D01 Qty 6 D02 Part # SPECIAL | NUT, CLIP-ON D02 Qty 7 D03 Part # 1/4-20 | BOLT, HEX DRIVE D03 Part # 1/4-20 | COLLAR D03 Qty 8 D34 Part # 3/8" | BOLT D34 Part # 3/8" | WASHER, PLAIN D34 Part # 3/8" | BUSHING, PLAIN D34 Part # 3/8" | WASHER, PLAIN D34 Part # 3/8" | NUT, SELF-LOCKING D34 Qty 2 D21 Part # 10-32 | WASHER, SPRING D21 Part # 10-32 | WASHER, PLAIN D21 Part # S.S | WASHER, PLAIN D21 Part # S.S | NUT, HEX D21 Qty 1 D26 Part # 5/16 | RIVET, UNIVERSAL HD D26 Qty 8 This is what I'm trying to get to: A B C D Title Param Part Qty D01 Part # 10-32 | BOLT, HEX DRIVE 6 D01 Part # 10-32 | COLLAR 6 D01 Qty 6 D02 Part # SPECIAL | NUT, CLIP-ON 7 D02 Qty 7 D03 Part # 1/4-20 | BOLT, HEX DRIVE 8 D03 Part # 1/4-20 | COLLAR 8 D03 Qty 8 D34 Part # 3/8" | BOLT 2 D34 Part # 3/8" | WASHER, PLAIN 2 D34 Part # 3/8" | BUSHING, PLAIN 2 D34 Part # 3/8" | WASHER, PLAIN 2 D34 Part # 3/8" | NUT, SELF-LOCKING 2 D34 Qty 2 D21 Part # 10-32 | WASHER, SPRING 1 D21 Part # 10-32 | WASHER, PLAIN 1 D21 Part # S.S | WASHER, PLAIN 1 D21 Part # S.S | NUT, HEX 1 D21 Qty 1 D26 Part # 5/16 | RIVET, UNIVERSAL HD 8 D26 Qty 8 There can be as few as 1 or as many as 7 rows of hardware needed to copy quantities to. I've tried pivot tables, but have not had any luck. Does anyone know if this even possible with a macro? Thanks for any help. Tony |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Copy and Fill to another column
welcome, pl post back after you've played it on your actuals over there.
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Tony S." wrote in message ... Hi Max, That may just do the trick. The only wrinkle is I have to got to a meeting and won't be able to play until tomorrow. I'll let you know. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Copy and Fill to another column
Max, it worked great! Thank you very much. I wish I knew where to learn stuff
like CTRL+SHIFT+ENTER to "array-enter" & confirm the formula as well as writing the formula. You were a big help. Tony "Max" wrote: welcome, pl post back after you've played it on your actuals over there. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Tony S." wrote in message ... Hi Max, That may just do the trick. The only wrinkle is I have to got to a meeting and won't be able to play until tomorrow. I'll let you know. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Copy and Fill to another column
Pleased to hear that, Tony. Thanks for the feedback.
If you browse around the postings/responses in this newsgroup & in ..worksheet.functions, you're bound to come across many other examples of array formulas. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Tony S." wrote: Max, it worked great! Thank you very much. I wish I knew where to learn stuff like CTRL+SHIFT+ENTER to "array-enter" & confirm the formula as well as writing the formula. You were a big help. Tony |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Copy or Fill Down Macro? | Excel Discussion (Misc queries) | |||
| Copy/Fill cell values in a column | Excel Worksheet Functions | |||
| Fill a column with the contents of another column based on a choic | Excel Discussion (Misc queries) | |||
| The fill feature in Excel that gives option to fill or copy | New Users to Excel | |||
| Value does not fill down (copy) properly. | Excel Discussion (Misc queries) | |||
![Tony S.[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
