Results 1 to 5 of 5

Thread: Left justifying data scattered across rows

  1. #1

    Unhappy Left justifying data scattered across rows



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi all

    I have been challenged and have had to concede defeat. I hope one of the gurus can assist with a solution.

    (See attachment. Sheet named Original is how data looks originally and Sheet named Final shows what I am wanting to achieve - the scattered data left justified)

    I have set up a worksheet so that column A contains yearly dates say A2 starts with 1/7/8 and increments by one day at a time down the column. Then columns B to D are initially blank. From column E on the columns are set up in multiples ie Col E contains Item, Column F contains Date (dates in these columns align with dates in Col A), column G contains Price. These three column headings are repeated many times across worksheet.

    Data is scattered across the rows so that each row contains only one set of data relevant to the headings.

    What I am wanting to do is to copy the scattered data into columns B to D (the blank ones) so that columns B to D is a left justified set of the scattered data set? Or is this too ambitious?

    Your assistance is very much appreciated.

    Regards

    mgerada
    Attached Files Attached Files

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,091
    Articles
    79
    Blog Entries
    14
    Hi there, and welcome to the forum.
    • Put this in B2: =IF(LEN(E2)=0,IF(LEN(H2)=0,IF(LEN(K2)=0,"",K2),H2),E2)
    • Copy it to C2 and D2
    • Format D2 as Currency
    • Copy B2:D2 down to the end of your table
    Hope it helps,
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Hi Ken

    Many thanks. It works well!!

    Can I now ask what if there are "n" multiples of the columns of scattered data? Is the formula you have suggested limited?

    Thanks

    mgerada

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,091
    Articles
    79
    Blog Entries
    14
    The formula I posted is limited to the three columns you could expand it but how many n's would you be looking at? It may be easier to reach to a macro to do this.

    Sent from my LG-E900h using Board Express
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  5. #5

    Smile

    Hi again Ken

    Many thanks for your help. I appreciate you getting back so quick.

    Regards

    mgerada

Posting Permissions

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