Using Vba to acquire lookup results in one cell

S

Sazzle14

New Member
Joined
Apr 12, 2011
Messages
20
Hi I used the formula below in trying to solve a puzzle I have, and it has got really close to what I need. It presented the results in col 1 and 2 below (C and D in your formula). However, What I need is to get the results separately i.e. Results for "7" in one cell in the spreadsheet and Results for "8" in another and the same with 9. So I need the formula to specify what it is looking for in column "BoX" but deliver the results in the same way it did for Column 1 and 2. I.e. it brings back multiple values with either a "," to separate or if possible a soft return {alt & Enter}
I know I am asking a lot but would so appreciate your help, I have tried Index and other VBA solutions and not having much luck.

[TABLE="class: cms_table, width: 432"]
<tbody>[TR]
[TD]Box[/TD]
[TD]Name[/TD]
[TD]Column1[/TD]
[TD]Column2[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD]Corel[/TD]
[TD="align: right"]7[/TD]
[TD]Corel , Steph , Jen , Amanda[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD]Steph[/TD]
[TD="align: right"]8[/TD]
[TD]Carol, Amer, Vicky, Charlotte, David[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD]Jen[/TD]
[TD="align: right"]9[/TD]
[TD]Kam, Gwen , Raj, David, Mark , Emma[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD]Amanda[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD]Carol[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD]Amer[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD]Vicky[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD]Charlotte[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD]David[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD]Kam[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD]Gwen[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD]Raj[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD]David[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD]Mark[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD]Emma[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]





quote_icon.png
Originally Posted by Tinbendr
Welcome to the board!

Code:
Code: 
Sub MergeGroups()
 Dim WS As Worksheet
 Dim Rng As Range
 Dim LastRow As Long
 Dim Ctr As Long
 Dim Temp$
     'Result Row
     Ctr = 1

     Set WS = ActiveWorkbook.ActiveSheet
     With WS
         'Last row of column A with data.
         LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

         'Loop through each item in Col A.
         For Each Rng In Range("A2:A" & LastRow)
             'Compare to next item down.  If equal build user string.
             If Rng.Offset(1, 0).Value = Rng.Value Then
                 Temp$ = Temp$ & Rng.Offset(0, 1).Value & ", "
             Else
                 'Servers no longer match.
                 Ctr = Ctr + 1
                 'Add last match
                 Temp$ = Temp$ & Rng.Offset(0, 1).Value
                 'Post to Col C & D.
                 .Range("C" & Ctr).Value = Rng.Value
                 .Range("D" & Ctr).Value = Temp$
                 'Clear Temp$
                 Temp$ = ""
             End If
         Next
     End With
 End Sub
 
Last edited by a moderator:

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Sort by date Sort by votes
If the macro is not doing what you want could you please explain what you do want & post some data showing the output result.
 
Upvote 0
Well I am finding it difficult to visually explain as the output that you see is infact (on some level) correct. At the moment it works through the list and systematically puts the data in to column 1 and 2. I need to be able to say put all 7's in cell G3, put all 8's in H4, put all 9s (I8).
In reality I will have all numbers up from 1-9 but I need to specify where they go, and I need in it to still put all results in one cell. Sorry I can't be more clear.

[TABLE="width: 891"]
<tbody>[TR]
[TD]Box[/TD]
[TD]Name[/TD]
[TD]Column1[/TD]
[TD]Column2[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD]Corel [/TD]
[TD="align: right"]7[/TD]
[TD]Corel , Steph , Jen , Amanda [/TD]
[TD][/TD]
[TD][/TD]
[TD]Corel , Steph , Jen , Amanda [/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD]Steph [/TD]
[TD="align: right"]8[/TD]
[TD]Carol, Amer, Vicky, Charlotte, David[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD]Jen [/TD]
[TD="align: right"]9[/TD]
[TD]Kam, Gwen , Raj, David, Mark , Emma[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD]Amanda [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD]Carol[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD]Amer[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Carol, Amer, Vicky, Charlotte, David[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD]Vicky[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD]Charlotte[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD]David[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD]Kam[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD]Gwen [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD]Raj[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Kam, Gwen , Raj, David, Mark , Emma[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD]David[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD]Mark [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD]Emma[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody><colgroup><col span="3"><col><col span="2"><col></colgroup>[/TABLE]
 
Upvote 0
Ok, how about
Code: 
Sub ConcatData()
   Dim Cl As Range
   
   With CreateObject("scripting.dictionary")
      For Each Cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
         If Not .exists(Cl.Value) Then
            .Add Cl.Value, Cl.Offset(, 1).Value
         Else
            .Item(Cl.Value) = .Item(Cl.Value) & Chr(10) & Cl.Offset(, 1).Value
         End If
      Next Cl
      Range("G3").Value = .Item(7)
      Range("H4").Value = .Item(8)
      Range("I8").Value = .Item(9)
   End With
End Sub
 
Upvote 0
Yes!!!!!!!!!!!!!!!!!!!!!!! that's done it!!!! Thank you thank you thank you!!!
[TABLE="width: 1019"]
<tbody>[TR]
[TD]Box[/TD]
[TD]Name[/TD]
[TD]Column1[/TD]
[TD]Column2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD]Corel [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD]Steph [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Corel
Steph
Jen
Amanda [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD]Jen [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Carol
Amer
Vicky
Charlotte
David[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD]Amanda [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD]Carol[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD]Amer[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD]Vicky[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Kam
Gwen
Raj
David
Mark
Emma[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD]Charlotte[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD]David[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD]Kam[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD]Gwen [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD]Raj[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD]David[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD]Mark [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD]Emma[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody><colgroup><col span="3"><col><col span="2"><col><col span="2"></colgroup>[/TABLE]
 
Upvote 0
Glad to help & thanks for the feedback
 
Upvote 0
Just one last quick question.
If I wanted to move the data into another worksheet would it be this modification to the code - or would it need referencing at the beginning

Worksheet("sheet1").Range("G3").Value = .Item(7)
 
Upvote 0
You must log in or register to reply here.

Similar threads

mehidy1437
  • Question Question
Requesting for experts comments suggestion on my vba code
Replies
5
Views
883
mehidy1437
mehidy1437
K
  • Solved
VBA Merge cells in range
Replies
3
Views
2K
Kra
K
C
  • Question Question
Ton of script in case anyone wants to review, maybe something useful to someone
Replies
0
Views
3K
cizzett
C
E
  • Question Question
Extracting part of a string in a column of cells (Site IDs) for each cell in a row and then moving down
Replies
0
Views
554
Excel_Ninja
E
S
  • Question Question
Adjust CSV Output to Remove Xtra Commas
Replies
0
Views
500
steve2115
S

Forum statistics

Threads
1,223,893
Messages
6,175,248
Members
452,623
Latest member
cliftonhandyman

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top