Solved

Transposing SQL Results in Excel

Posted on 2015-02-10
10
110 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
[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
  • 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

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

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

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

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
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…

730 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