Left justifying data scattered across rows

mgerada

New member
Joined
Nov 13, 2011
Messages
3
Reaction score
0
Points
0
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
 

Attachments

  • Sample worksheets.xlsx
    21 KB · Views: 16
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,
 
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
 
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
 
Hi again Ken

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

Regards

mgerada
 
Back
Top