how to change the Excel sheet data format format

how to change the Excel sheet data format format attached from the the databbase sheet format to the final sheet format.

Regards,

Dallag
test.xlsx
Mohammed DallagPetroleum ConsultantAsked:
Who is Participating?
 
Ejgil HedegaardConnect With a Mentor Commented:
Try this

Option Explicit

Sub ShowFlowValues()
    Dim wsData As Worksheet, wsFinal As Worksheet
    Dim RowData As Long, RowFinal As Long
    Const FinalColWell = 1, FinalColDate = 2, FinalColOil = 3, FinalColWater = 4, FinalColGas = 5
    Const DataColWell = 1, DataColDescriptor = 3
    Dim DataColDate As Integer
    Dim Header As Variant
    Dim RowStart As Long, RowEnd As Long, ColorSelect As Long
    Const ColorHeader = 8839904, Color1 = 13434828, Color2 = 10079487
    
    Header = Array("UI", "Date", "MPFM VOL FLOW OF OIL", "MPFM VOL FLOW OF WATER", "MPFM VOL FLOW OF GAS")
    RowFinal = 1
    Set wsData = Worksheets("Database")
    Set wsFinal = Worksheets("Final")
    
    Application.ScreenUpdating = False
    wsFinal.Select
    wsFinal.Cells.Clear
    wsFinal.Range("A1").Resize(1, UBound(Header) + 1) = Header
    
    'Get the data
    For RowData = 2 To wsData.Range("A1").CurrentRegion.Rows.Count
        If wsData.Cells(RowData, DataColDescriptor) = "MPFM VOL FLOW OF OIL" Then
            For DataColDate = 5 To wsData.Range("A1").CurrentRegion.Columns.Count
                RowFinal = RowFinal + 1
                wsFinal.Cells(RowFinal, FinalColWell) = wsData.Cells(RowData, DataColWell)
                wsFinal.Cells(RowFinal, FinalColDate) = wsData.Cells(1, DataColDate)
                wsFinal.Cells(RowFinal, FinalColOil) = wsData.Cells(RowData, DataColDate)
                wsFinal.Cells(RowFinal, FinalColWater) = wsData.Cells(RowData + 1, DataColDate)
                wsFinal.Cells(RowFinal, FinalColGas) = wsData.Cells(RowData + 2, DataColDate)
            Next DataColDate
        End If
    Next RowData
    
    'Format Final sheet
    wsFinal.Range(Cells(1, FinalColDate), Cells(RowFinal, FinalColDate)).NumberFormat = "d/mmm/yy"
    With wsFinal.Range(Cells(1, 1), Cells(RowFinal, FinalColGas))
        .Borders.Weight = xlThin
        .HorizontalAlignment = xlCenter
    End With
    wsFinal.Range(Cells(1, 1), Cells(1, FinalColGas)).Interior.Color = ColorHeader
    
    ColorSelect = Color2
    RowFinal = 2
    Do While Len(wsFinal.Cells(RowFinal, FinalColWell)) > 0
        RowStart = RowFinal
        RowEnd = RowStart
        Do While wsFinal.Cells(RowEnd, FinalColWell) = wsFinal.Cells(RowStart, FinalColWell)
            RowEnd = RowEnd + 1
        Loop
        RowEnd = RowEnd - 1
        If ColorSelect = Color1 Then
            ColorSelect = Color2
        Else
            ColorSelect = Color1
        End If
        wsFinal.Range(Cells(RowStart, FinalColWell), Cells(RowEnd, FinalColGas)).Interior.Color = ColorSelect
        RowFinal = RowEnd + 1
    Loop
End Sub

Open in new window

0
 
Naresh PatelTraderCommented:
Try this formula in Cell G2
=INDEX(DATABASE!$A$1:$AF$3159,MATCH(1,(DATABASE!$A$1:$A$3159=Final!$A2)*(DATABASE!$C$1:$C$3159=Final!G$1),0),MATCH(Final!$B2,DATABASE!$A$1:$AF$1,0))

Open in new window

and G1="MPFM VOL FLOW OF OIL".



Thank You
0
 
Naresh PatelTraderCommented:
sorry I guess i interpret wrong .....
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Naresh PatelTraderCommented:
What you want exactly?
0
 
Naresh PatelTraderCommented:
Read your question again and what I understood - you want to change orientation of data     is that right?
if you want to archive this via formulas?
0
 
Mohammed DallagPetroleum ConsultantAuthor Commented:
I want to read the first sheet and generate the second sheet (final)
0
 
Naresh PatelTraderCommented:
And you what to  do it by formula (little manual process) or By VBA (Automation)?

By formula - reply me back
By VBA - Click Request Attention button.


Thanks
0
 
Mohammed DallagPetroleum ConsultantAuthor Commented:
by VBA (Automation
0
 
Naresh PatelTraderCommented:
By VBA - Click Request Attention button.

Request

Thank You
0
 
Mohammed DallagPetroleum ConsultantAuthor Commented:
Excellent thank you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.