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
Solved

Transposing SQL Results in Excel

Posted on 2015-02-10
10
109 Views
Last Modified: 2016-02-10
I have two columns of data I'm importing into Excel from SQL.

Column A (Product) has values that will appear 1 to x number of times based on matching results from Column B (Components).

For example:

Product          Component
P1                   C1
P1                   C5
P1                   C8
P1                   C9
P2                   C2
P2                   C3
P3                   C1
P3                   C2
P3                   C3
P4                   C1

Open in new window


What I'm trying to accomplish is to get the components for each product into a horizontal list:

P1     C1     C5     C8     C9
P2     C2     C3
P3     C1     C2     C3
P4     C1

Open in new window


I'm dealing with about 50,000 rows of data.

I'm not sure how to transpose this data. Any suggestions?
0
Comment
Question by:MIGINC
  • 5
  • 5
10 Comments
 
LVL 12

Expert Comment

by:FarWest
ID: 40601250
do you have maximum number of possible columns?
0
 

Author Comment

by:MIGINC
ID: 40601265
I don't think I have any products that are made up of more than 10 components.
0
 
LVL 12

Accepted Solution

by:
FarWest earned 500 total points
ID: 40601387
try this excel vba script

Sub TransposeValues()
Dim TargetValue As String
Dim LastRow As Integer, Targetrow, TargetCol, ii
LastRow = ActiveSheet.UsedRange.Rows.Count
Targetrow = 2
TargetValue = Range("A2").Value
TargetCol = 2
 For ii = 3 To LastRow + 1
  If Cells(ii, 1) = TargetValue Then
  TargetCol = TargetCol + 1
   Cells(Targetrow, TargetCol) = Cells(ii, 2)
  Cells(ii, 3) = "Delete"
  Else
  Targetrow = ii
  TargetCol = 2
  TargetValue = Cells(ii, 1)
  End If
 Next
 For ii = LastRow + 1 To 1 Step -1
 If Cells(ii, 3) = "Delete" Then
 Rows.EntireRow(ii).Delete
 End If
 Next
End Sub

Open in new window

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:MIGINC
ID: 40601494
I get a popup window titled "Microsoft Visual Basic for Applications" with the error message "Overflow".

I grabbed a section of items instead of the whole list and it seems to work. I could split up the worksheet into multiple sheets if I need to, but shouldn't Excel be able to handle 50,000 rows?

Another question: It just so happened that one of the items I grabbed actually has 10 components. Is there a limit in this code that it's only going to find up to 10? I only ask because of your first question. I don't see anything in your code that would limit it, but I'm not a VBA guy.
0
 
LVL 12

Expert Comment

by:FarWest
ID: 40601544
no column limit, I asked the question because I had a none vba solution in mind, and found it needs a lot of user intervention like sorting, deleting, and found vba will be better
for overflow issue try to replace all integer datatype to long rhis will solve 32k limit

Open in new window

0
 

Author Comment

by:MIGINC
ID: 40601566
I'm sorry - I don't understand what you mean by converting integer to long. My 2 columns are just text columns.
0
 
LVL 12

Expert Comment

by:FarWest
ID: 40601575
in vba change
dim anyvar as integer
to
as long
0
 

Author Comment

by:MIGINC
ID: 40601582
That did the trick.

Thanks for your help!
0
 

Author Closing Comment

by:MIGINC
ID: 40601584
Excellent response time and great follow-up help!
0
 
LVL 12

Expert Comment

by:FarWest
ID: 40601599
you are welcome, and thanks a lot for this encouraging feedback
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

856 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