Solved

how to change the Excel sheet data format format

Posted on 2014-03-19
10
381 Views
Last Modified: 2014-03-21
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
0
Comment
Question by:dallagmm
  • 6
  • 3
10 Comments
 
LVL 8

Expert Comment

by:itjockey
ID: 39939160
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
 
LVL 8

Expert Comment

by:itjockey
ID: 39939174
sorry I guess i interpret wrong .....
0
 
LVL 8

Expert Comment

by:itjockey
ID: 39941350
What you want exactly?
0
 
LVL 8

Expert Comment

by:itjockey
ID: 39941381
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
 

Author Comment

by:dallagmm
ID: 39941384
I want to read the first sheet and generate the second sheet (final)
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 8

Expert Comment

by:itjockey
ID: 39941442
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
 

Author Comment

by:dallagmm
ID: 39941445
by VBA (Automation
0
 
LVL 8

Expert Comment

by:itjockey
ID: 39941455
By VBA - Click Request Attention button.

Request

Thank You
0
 
LVL 21

Accepted Solution

by:
Ejgil Hedegaard earned 500 total points
ID: 39946054
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
 

Author Closing Comment

by:dallagmm
ID: 39946742
Excellent thank you
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now