Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# how to change the Excel sheet data format format

Posted on 2014-03-19
Medium Priority
396 Views
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
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
• 6
• 3

LVL 8

Expert Comment

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))
``````
and G1="MPFM VOL FLOW OF OIL".

Thank You
0

LVL 8

Expert Comment

ID: 39939174
sorry I guess i interpret wrong .....
0

LVL 8

Expert Comment

ID: 39941350
What you want exactly?
0

LVL 8

Expert Comment

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

ID: 39941384
I want to read the first sheet and generate the second sheet (final)
0

LVL 8

Expert Comment

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

ID: 39941445
by VBA (Automation
0

LVL 8

Expert Comment

ID: 39941455
By VBA - Click Request Attention button.

Thank You
0

LVL 23

Accepted Solution

Ejgil Hedegaard earned 2000 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 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

'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
``````
0

Author Closing Comment

ID: 39946742
Excellent thank you
0

## Featured Post

Question has a verified solution.

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

This article describes a serious pitfall that can happen when deleting shapes using VBA.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custoâ€¦
###### Suggested Courses
Course of the Month12 days, 4 hours left to enroll