+ Reply to Thread
Results 1 to 19 of 19

Pattern Copying Issue

  1. #1
    Registered User
    Join Date
    10-23-2016
    Location
    london
    MS-Off Ver
    2013
    Posts
    9

    Pattern Copying Issue

    Hello everyone here,

    I have a complex issue and I need your help to solve it together:


    I have excel sheet has over 400,000 row extracted from a company database.

    The sheet has many Ref. Start with Ho1 and the data below for each reference would like to make it as follow:


    there are 2 columns F (Descr 1) & G (Descr 2) and i want to copy the (cell B6 to column F and cell D6 to Column G) beside numbers as shown in attachment and i want the same description to be draged below till the last number for Ref no


    anybody can help with equation, VBA anything i will be most appreciated.
    Attached Files Attached Files
    Last edited by Sharkawy; 10-23-2016 at 01:08 PM. Reason: add attachment

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,890

    Re: Pattern Copying Issue

    Will you please attach a sample Excel workbook? Please don't attach a picture of one.

    1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

    2. Make sure that your desired solution is also shown (mock up the results manually).

    3. Make sure that all confidential information is removed first!!

    4. Try to avoid using merged cells. They cause lots of problems!

    Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Registered User
    Join Date
    10-23-2016
    Location
    london
    MS-Off Ver
    2013
    Posts
    9

    Re: Pattern Copying Issue

    the excel sheet is very confidential and i give you all i want to do, i hope to got my point
    and thx for ur advice

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,890

    Re: Pattern Copying Issue

    Comment withdrawn: attachment attached by OP
    Last edited by Glenn Kennedy; 10-23-2016 at 01:07 PM.

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,890

    Re: Pattern Copying Issue

    In F7, copied down:
    =IF($D7="","",IF($A6="Ref no",B6,F6))

    In G7, copied down:
    =IF($D7="","",IF($A6="Ref no",D6,G6))
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    10-23-2016
    Location
    london
    MS-Off Ver
    2013
    Posts
    9

    Re: Pattern Copying Issue

    Quote Originally Posted by Glenn Kennedy View Post
    In F7, copied down:
    =IF($D7="","",IF($A6="Ref no",B6,F6))

    In G7, copied down:
    =IF($D7="","",IF($A6="Ref no",D6,G6))

    You are very brilliant thank you so much my friend and i am very sorry for anything missunderstanding
    If u dont mind, explain to me this parameter $D7="","" as i dont understand

  7. #7
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,890

    Re: Pattern Copying Issue

    So.
    IF($D7="",""
    If D7 is blank, put a blank. the only time D rows are blank is when it's about to change from one HO1 to another.

    the rest of the formula IF($A6="Ref no",B6,F6) copies the value from the cell above UNLESS the A cell contains "Ref no" (which indicates a new Ho1 code). when "Ref No" is in the a cell, it returns the new HO1 code.

    Also, apologies for being a bit abrupt with you. I thought that you were refusing to post a sheet (as I didn't look back at the first post).
    Last edited by Glenn Kennedy; 10-23-2016 at 01:34 PM.

  8. #8
    Registered User
    Join Date
    10-23-2016
    Location
    london
    MS-Off Ver
    2013
    Posts
    9

    Re: Pattern Copying Issue

    Did u have any idea to highlighted the dublicated amounts in rows not in columns ?

  9. #9
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,890

    Re: Pattern Copying Issue

    Quote Originally Posted by Sharkawy View Post
    Did u have any idea to highlighted the dublicated amounts in rows not in columns ?
    Sorry, I don't quite understand what you mean by this.....

  10. #10
    Registered User
    Join Date
    10-23-2016
    Location
    london
    MS-Off Ver
    2013
    Posts
    9

    Re: Pattern Copying Issue

    Quote Originally Posted by Glenn Kennedy View Post
    Sorry, I don't quite understand what you mean by this.....
    I have Two columns D & E i want to delete any duplicates numbers if they are in one row (Tip: Matching Debit with Credit something related to accounting)

  11. #11
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,890

    Re: Pattern Copying Issue

    Mmm. I'm still not sure what you want. can you amend your sheet to show the new problem and what result you want to see?

  12. #12
    Registered User
    Join Date
    10-23-2016
    Location
    london
    MS-Off Ver
    2013
    Posts
    9

    Re: Pattern Copying Issue

    Quote Originally Posted by Glenn Kennedy View Post
    Mmm. I'm still not sure what you want. can you amend your sheet to show the new problem and what result you want to see?
    Solved, thank u my friend for ur interest and efforts

    Quote Originally Posted by Glenn Kennedy
    IF($D7="",""
    If D7 is blank, put a blank. the only time D rows are blank is when it's about to change from one HO1 to another.
    D7 is not Blank and why u put comma ","

  13. #13
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,890

    Re: Pattern Copying Issue

    The notation for blank is ""
    the , is an argument separator.

    If D7 ="" means if D7 is blank
    , separates the first bit from the second bit - like taking a breath...

    "" means put a blank

    the whole thing:

    =if(d7="","",remainder of the formula)

    If D7=blank, put a blank, otherwise, follow the rest of the formula

  14. #14
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,890

    Re: Pattern Copying Issue

    Select one of the cells with my formula in it. Go to Formulas/formula auditing/evaluate formula. Then watch excel go through the steps in the formula as you click "evaluate formula".

  15. #15
    Registered User
    Join Date
    10-23-2016
    Location
    london
    MS-Off Ver
    2013
    Posts
    9

    Re: Pattern Copying Issue

    thank u so much Glenn, got the point

  16. #16
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,890

    Re: Pattern Copying Issue

    OK. glad to have helped.

  17. #17
    Registered User
    Join Date
    10-23-2016
    Location
    london
    MS-Off Ver
    2013
    Posts
    9

    Re: Pattern Copying Issue

    did you know the code if i would like to make it with VBA command button

    regarding;

    In F7, copied down:
    =IF($D7="","",IF($A6="Ref no",B6,F6))

    In G7, copied down:
    =IF($D7="","",IF($A6="Ref no",D6,G6))

  18. #18
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,890

    Re: Pattern Copying Issue

    I know nothing about VBA....

  19. #19
    Registered User
    Join Date
    10-23-2016
    Location
    london
    MS-Off Ver
    2013
    Posts
    9

    Re: Pattern Copying Issue

    Quote Originally Posted by Glenn Kennedy View Post
    I know nothing about VBA....
    i know alittle bit but not a professional with it

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Copying a formula with a pattern
    By jaydotjaymill in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-06-2015, 04:20 PM
  2. [SOLVED] Copying a formula with that follows a pattern
    By Animosity in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-22-2013, 12:19 PM
  3. Pattern matching and then copying PasteSpecial to another sheet
    By hanott1 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-12-2011, 04:31 PM
  4. copying pattern from one column to another
    By chasinthetrane in forum Excel General
    Replies: 2
    Last Post: 12-28-2007, 11:57 AM
  5. copying formula pattern
    By marvinks in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-12-2007, 12:29 PM
  6. Copying cells based on pattern
    By nougain in forum Excel General
    Replies: 4
    Last Post: 03-20-2007, 05:11 PM
  7. [SOLVED] Copying font and pattern dynamically
    By RTP in forum Excel General
    Replies: 1
    Last Post: 06-23-2005, 07:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1