Vlookup

[Guest]

Hi,

I have a set of fields that are structutred something like:
A B C
1 one unique data 1
2 one unique data 2
3 three unique data 3
4 two unique data 4
5 one unique data 5
6 three unique data 6

I have no way of knowing how many times each of the non-unique values in
column B will occur, nor which order they will occur in.

The unique data sets (column C) may be blank cells.

What I want to do is use a VLOOKUP type function to combine the unique data
sets into a single row, so that the output looks like:

A B C D
One unique data 1 unique data 2 unique data 5
Two unique data 4
Three unique data 3 unique data 6

Note that I do not need the unique identifier in the output, but it doesn't
matter if it is there.

Any useful suggestions would be much appreciated.

If it makes any difference, this is all in XL 2003.

TIA

Dave

 

[Guest]

I think a Pivot Table is the tool you are looking for.

 

[Guest]

I think a Pivot Table is the tool you are looking for.

 

[Dave Peterson]

Since your unique data looks like it could be alphanumeric, I don't think a
pivottable will work.

But you could use a macro.

This sorts the original worksheet by column B (column A is essentially ignored,
right?). Then it creates a new worksheet with the new layout.

Option Explicit
Sub testme01()

Dim CurWks As Worksheet
Dim NewWks As Worksheet
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim CurKey As Variant
Dim oRow As Long
Dim oCol As Long

Set CurWks = Worksheets("Sheet1")
Set NewWks = Worksheets.Add

With CurWks
With .Range("a:c")
.Sort key1:=.Columns(2), order1:=xlAscending, _
key2:=.Columns(3), order2:=xlAscending, _
header:=xlNo
End With

FirstRow = 1
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
CurKey = "somethingthatdoesnotexistinyourtablehere"

oRow = 0
oCol = 2
For iRow = FirstRow To LastRow
If Trim(.Cells(iRow, "C").Value) = "" Then
'skip this row
Else
If .Cells(iRow, "b").Value = CurKey Then
oCol = oCol + 1
NewWks.Cells(oRow, oCol).Value = .Cells(iRow, "C").Value
Else
oRow = oRow + 1
oCol = 2
CurKey = .Cells(iRow, "B").Value
NewWks.Cells(oRow, "A").Value = .Cells(iRow, "B").Value
NewWks.Cells(oRow, oCol).Value = .Cells(iRow, "C").Value
End If
End If
Next iRow
End With

NewWks.UsedRange.Columns.AutoFit
End Sub

 

[Dave Peterson]

ps.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Guest]

Actually, a Pivot Table is a very workable, perfect solution to this problem,
and only takes a few seconds to set up. I'm assuming that "Risky Dave" is
looking for the results, not necessarily in the format he suggested. If
anyone would like to see this very case set up as a Pivot Table, email me at
(e-mail address removed), and I will send you the file by return email.

Aside to Dave Peterson: Dave, I tried to send the file I mentioned above to
you, but apparently the email address attached to your Discussion Group
log-in is no longer valid.

 

[Dave Peterson]

I don't open unsolicited files.

And you got text values to show up in the data portion of your pivottable?

How?

 

[Guest]

Actually, this was the rejection notice I got:

Hi. This is the qmail-send program at yahoo.com.
I'm afraid I wasn't able to deliver your message to the following
addresses.
This is a permanent error; I've given up. Sorry it didn't work out.

<[email protected]>:
Sorry, I couldn't find any host named verizonXSPAM.net.


To answer your question about text values, I didn't do anything special; it
just worked. I'm not a Pivot Table expert -- just done a few -- but they are
really simple to do. If there is some way I can send you the file, I would be
happy to do so. As for Pivot Tables, my impression is that they are one of
the most powerful best-kept secrets in Excel.

 

[Dave Peterson]

Please don't post anyone's address. Many people go out of their way to munge
their email addresses so that spambots can't grab email addresses from
newsgroups.

Maybe you could explain what your test data looked like and how you did it--in
plain old text.

Actually, this was the rejection notice I got:

Hi. This is the qmail-send program at yahoo.com.
I'm afraid I wasn't able to deliver your message to the following
addresses.
This is a permanent error; I've given up. Sorry it didn't work out.

<[email protected]>:
Sorry, I couldn't find any host named verizonXSPAM.net.

To answer your question about text values, I didn't do anything special; it
just worked. I'm not a Pivot Table expert -- just done a few -- but they are
really simple to do. If there is some way I can send you the file, I would be
happy to do so. As for Pivot Tables, my impression is that they are one of
the most powerful best-kept secrets in Excel.

 

[Peo Sjoblom]

Petersod eh?


<g>


Peo

Please don't post anyone's address. Many people go out of their way to
munge
their email addresses so that spambots can't grab email addresses from
newsgroups.

Maybe you could explain what your test data looked like and how you did
it--in
plain old text.

 

[Dave Peterson]

When did you move to Canada, eh?
<hehehe>

Petersod eh?

<g>

Peo

 

[Guest]

My test data looks like this:

Age Name
one unique data 1
one unique data 2
three unique data 3
two unique data 4
one unique data 5
three unique data 6


This is exactly what "Risky Dave" had, except I added headers. Then I just
ran the PivotTable wizard. This took maybe 30 seconds. This was the result:


Age Name Count of Name
one 3
unique data 1 1
unique data 2 1
unique data 5 1

two 1
unique data 4 1

three 2
unique data 3 1
unique data 6 1

Grand Total 6

Perhaps you didn't realize that anyone can get your email address just by
clicking on your name when you post a comment in this discussion group,
although yours doesn't seem to be a current or valid one.

By the way, Dave, could you expain in plain old text what it means to "munge
their email addresses so that spambots can't grab email addresses"?

 

[Dave Peterson]

I read the other Dave's request a little differently.

I read it that he wants this kind of output:

A B C D
One unique data 1 unique data 2 unique data 5
Two unique data 4
Three unique data 3 unique data 6

Where there is a single row for each unique value in column B of the original
data. Then there is a value in column B, C, D, E... for each of those
values--as many as required for that value.

By munging an email address, the poster is trying to make it difficult for
spambots to glean his/her email from these newsgroups.

For example, the email address you see for me is:
(e-mail address removed)

My real email address does not have XSPAM in it. (Please don't post it.)

MS explains it here:
http://www.microsoft.com/library/ga...ales/help/help_en-US.htm#AboutEmailAndPrivacy

My test data looks like this:

Age Name
one unique data 1
one unique data 2
three unique data 3
two unique data 4
one unique data 5
three unique data 6

This is exactly what "Risky Dave" had, except I added headers. Then I just
ran the PivotTable wizard. This took maybe 30 seconds. This was the result:


Age Name Count of Name
one 3
unique data 1 1
unique data 2 1
unique data 5 1

two 1
unique data 4 1

three 2
unique data 3 1
unique data 6 1

Grand Total 6

Perhaps you didn't realize that anyone can get your email address just by
clicking on your name when you post a comment in this discussion group,
although yours doesn't seem to be a current or valid one.

By the way, Dave, could you expain in plain old text what it means to "munge
their email addresses so that spambots can't grab email addresses"?

 

[Guest]

Thank you, Dave. I get it now, and I agree with your concern about email
abuse. I, too, never open email unless I am very certain it is clean.

Regarding the issue at hand, as I said in my earlier posting under this
topic, "I'm assuming that "Risky Dave" is looking for the results, not
necessarily in the format he suggested." If the format he mentioned is
imperative, you could still use a Pivot Table to get there by using Paste
Special/Transpose to convert columns to rows, although this might require a
little cleaning up. This may or may not be very practical, depending on the
volume of data, etc. A real authority on Pivot Tables might be able to get
there without the additional steps.

Visual Basic modules and Macros are fantastic devices for doing things that
can't be done readily with the functions and tools built into Excel (and
thankfully there are generous folks like yourself who are willing to come to
the aid of people like Risky Dave and me who cry out for solutions to
difficult tasks), but sometimes there is more than one way to get it done.

 

[Guest]

Dave & Traveller,

Thanks for your responses and the links.

I should have said that I was trying to get the spreadsheet to update itself
automatically so that the end users (who won't be me) doesn't actually have
to do anything (less likely to break something!). What I am actually building
is a table pulling data from multiple worksheets and organising it in a
specific way so that it can then be the source of an automated reporting
function.

I guess this is a classic case of trying to get XL to do something (build a
one-to-many relationship) that would be better done in Access :-(

I'm now going to try both solutions (macro & pivot table) and see which I
prefer.

Again, my thanks to you both for your time.

 

[Dave Peterson]

It's always nice to have options.

Thank you, Dave. I get it now, and I agree with your concern about email
abuse. I, too, never open email unless I am very certain it is clean.

Regarding the issue at hand, as I said in my earlier posting under this
topic, "I'm assuming that "Risky Dave" is looking for the results, not
necessarily in the format he suggested." If the format he mentioned is
imperative, you could still use a Pivot Table to get there by using Paste
Special/Transpose to convert columns to rows, although this might require a
little cleaning up. This may or may not be very practical, depending on the
volume of data, etc. A real authority on Pivot Tables might be able to get
there without the additional steps.

Visual Basic modules and Macros are fantastic devices for doing things that
can't be done readily with the functions and tools built into Excel (and
thankfully there are generous folks like yourself who are willing to come to
the aid of people like Risky Dave and me who cry out for solutions to
difficult tasks), but sometimes there is more than one way to get it done.

 

[Guest]

You are very welcome. Thanks for the feedback. Good luck!