Thread: Left justifying data scattered across rows

1. Left justifying data scattered across rows

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

2. 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,

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

4. 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

5. Hi again Ken

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

Regards