Solved

how to change the Excel sheet data format format

Posted on 2014-03-19
10
392 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:Mohammed Dallag
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
10 Comments
 
LVL 8

Expert Comment

by:Naresh Patel
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:Naresh Patel
ID: 39939174
sorry I guess i interpret wrong .....
0
 
LVL 8

Expert Comment

by:Naresh Patel
ID: 39941350
What you want exactly?
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 8

Expert Comment

by:Naresh Patel
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:Mohammed Dallag
ID: 39941384
I want to read the first sheet and generate the second sheet (final)
0
 
LVL 8

Expert Comment

by:Naresh Patel
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:Mohammed Dallag
ID: 39941445
by VBA (Automation
0
 
LVL 8

Expert Comment

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

Request

Thank You
0
 
LVL 22

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:Mohammed Dallag
ID: 39946742
Excellent thank you
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

738 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