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

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,521
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
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,
 

mgerada

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

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,521
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
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
 

mgerada

New member
Joined
Nov 13, 2011
Messages
3
Reaction score
0
Points
0
Hi again Ken

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

Regards

mgerada
 
Top