Arrange Cell Data

• pl refer attached file
here i get certain code , date , price in column a, b, c respectively which i have to arrange like code in column a, then unique data of prices in column b to end (as available data) then price to be plotted accorss the data
e.g.

code 01-01-2001---02-01-2001------03-01-2001
a 50 60 70
b 90 400 500
c 45 54 74

got it as this kind of data is helpful for my index/match function

can anyone kindly give me hint or VBA code to autoformat data

note condition will always be same

Files

• Re: Cell Formating

this is one formula

put it in cell B2 of Required Format sheet and copy

=IF(ISERROR(INDEX('Raw Data'!\$C\$2:\$C\$1616,MAX(SUMPRODUCT((('Raw Data'!\$A\$2:\$A\$1616)=B\$1)*(('Raw Data'!\$B\$2:\$B\$1616)=\$A2)*ROW('Raw Data'!\$A\$2:\$A\$1616)))-1)),"",INDEX('Raw Data'!\$C\$2:\$C\$1616,MAX(SUMPRODUCT((('Raw Data'!\$A\$2:\$A\$1616)=B\$1)*(('Raw Data'!\$B\$2:\$B\$1616)=\$A2)*ROW('Raw Data'!\$A\$2:\$A\$1616)))-1))

pl note this is an array formula and needs to be confirmed by a Ctrl+Shift+Enter

• Re: Cell Formating

Hi anandbhora,
[code]
Sub FormatCells()
Dim iRow As Long
iRow = Range("C65536").End(xlUp).Row
Range("C2:C" & iRow).Select
Selection.NumberFormat = "\$#,##0.00"
End Sub
[/end code]

Bill

Bill

• Re: Cell Formating

pangolin

u r bravo man
thanks for your precious time devoted to solve my query

this had saved my hours of work

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!