?
Solved

Transposing SQL Results in Excel

Posted on 2015-02-10
10
Medium Priority
?
120 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 2000 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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 

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

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Use Windows Task Scheduler to print a Word document weekly so your printer ink won't dry out.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

800 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