Separating and condensing data from table

D

DHChemist

New Member
Joined
Feb 9, 2015
Messages
3
Hi,

Hopefully someone can help me with a problem I can't seem to get my head around.

Here's an example sheet: http://i.imgur.com/QWnpSLM.png

I type names into A2:A8 and use VLOOKUP to fill B2:B8 with codes from H2:H10, so far so good. But then I'd like to generate a table in D2:E8 which lists the values from B2:B8 individually without repeats, then use a second VLOOKUP to retrieve what each code means from K2:K11. Hopefully D16:E22 gives a better example of what I'm trying to achieve.


The number of codes in B2:B8 can vary, and whilst the source data is formatted with hyphens between each code, if another delimiter makes things easier that could probably be changed.



I'd rather achieve this using formulae than having to turn to VBA, pretty much because my experience with VBA is practically non-existent. The end goal is that I could type into the Name column, and the rest of the columns auto-populate and are formatted without need to manually format anything.

Thanks for any help you can offer!
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Sort by date Sort by votes
DHChemist,

Welcome to the MrExcel forum.

1. What version of Excel and Windows are you using?

2. Are you using a PC or a Mac?

3. What is the VLOOKUP formula you are using in cell B2?


You are posting a picture/graphic. This means that if this was a problem where one needed to use your data, anyone trying to help you would have to enter the data manually. That makes no sense.

To start off, and, so that we can get it right on the first try:

Can you post a screenshot of the actual raw data worksheet?

And, can you post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

To post a small screen shot try one of the following:

Excel Jeanie
Download

MrExcel HTML Maker
https://onedrive.live.com/?cid=8cffdec0ce27e813&sc=documents&id=8CFFDEC0CE27E813!189

Borders-Copy-Paste
http://www.mrexcel.com/forum/about-board/444901-how-create-table-like-aladin.html#post2198045

To test the above:
Test Here
 
Upvote 0
hiker95 said:
DHChemist,

Welcome to the MrExcel forum.

1. What version of Excel and Windows are you using?

2. Are you using a PC or a Mac?

3. What is the VLOOKUP formula you are using in cell B2?


You are posting a picture/graphic. This means that if this was a problem where one needed to use your data, anyone trying to help you would have to enter the data manually. That makes no sense.

To start off, and, so that we can get it right on the first try:

Can you post a screenshot of the actual raw data worksheet?

And, can you post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

To post a small screen shot try one of the following:

Excel Jeanie
Download

MrExcel HTML Maker
https://onedrive.live.com/?cid=8cffdec0ce27e813&sc=documents&id=8CFFDEC0CE27E813!189

Borders-Copy-Paste
http://www.mrexcel.com/forum/about-board/444901-how-create-table-like-aladin.html#post2198045

To test the above:
Test Here
Click to expand...

Woops, apologies.

1) Excel 2013 on Windows 7 Enterprise
2) PC
3) B2 lookup: =VLOOKUP(A2,G2:H11,2,FALSE)

Raw data worksheet:
NameCodesCodesDefinitionsNameCodesCodeDefinitions
Harry1-9Harry1-91House
James1-6-92Cat
Paul3-43Dog
Matt1-3-5-6-74Fish
Andy5Ball
6Jam
7Tea
8Coffee
9Cream
10Toy

<tbody>
</tbody>

Desired results:

NameCodesCodesDefinitionsNameCodesCodeDefinitions
Paul3,41HouseHarry1-91House
Harry1,93DogJames1-6-92Cat
James1,6,94FishPaul3-43Dog
6JamMatt1-3-5-6-74Fish
9Cream5Ball
6Jam
7Tea
8Coffee
9Cream
10Toy

<tbody>
</tbody>

B2 (and copied down that column to B8) is the only formulae currently in the table. It's the D and E columns in the results I'm looking to fill.

Unfortunately can't download the add-ins to give a better table, the joys of a tightly managed desktop. I think this is formatted correctly though.
 
Upvote 0
DHChemist,

Thanks for the screenshots.

I have changed your VLOOKUP formulae in range B2:B8, and, in range E2:E11.

Here is a macro solution for you to consider.

Sample raw data in the active worksheet:


Excel 2007
ABCDEFGHIJK
1NameCodesCodesDefinitionsNameCodesCodeDefinitions
2Paul3-4 Harry1-91House
3Harry1-9James1-6-92Cat
4James1-6-9Paul3-43Dog
5Matt1-3-5-6-74Fish
6Andy5Ball
76Jam
87Tea
98Coffee
109Cream
1110Toy
12
Sheet1
Cell Formulas
RangeFormula
B2=IF(ISERROR(VLOOKUP(A2,$G$2:$H$11,2,FALSE)),"",VLOOKUP(A2,$G$2:$H$11,2,FALSE))
E2=IF(ISERROR(VLOOKUP(D2,$J$2:$K$11,2,FALSE)),"",VLOOKUP(D2,$J$2:$K$11,2,FALSE))


If we run the macro, we get this:


Excel 2007
ABCDEFGHIJK
1NameCodesCodesDefinitionsNameCodesCodeDefinitions
2Paul3-41HouseHarry1-91House
3Harry1-93DogJames1-6-92Cat
4James1-6-94FishPaul3-43Dog
56JamMatt1-3-5-6-74Fish
69CreamAndy5Ball
76Jam
87Tea
98Coffee
109Cream
1110Toy
12
Sheet1
Cell Formulas
RangeFormula
B2=IF(ISERROR(VLOOKUP(A2,$G$2:$H$11,2,FALSE)),"",VLOOKUP(A2,$G$2:$H$11,2,FALSE))
E2=IF(ISERROR(VLOOKUP(D2,$J$2:$K$11,2,FALSE)),"",VLOOKUP(D2,$J$2:$K$11,2,FALSE))


if we add Andy to cell A5, we get this:


Excel 2007
ABCDEFGHIJK
1NameCodesCodesDefinitionsNameCodesCodeDefinitions
2Paul3-41HouseHarry1-91House
3Harry1-93DogJames1-6-92Cat
4James1-6-94FishPaul3-43Dog
5Andy06JamMatt1-3-5-6-74Fish
69CreamAndy5Ball
76Jam
87Tea
98Coffee
109Cream
1110Toy
12
Sheet1
Cell Formulas
RangeFormula
B2=IF(ISERROR(VLOOKUP(A2,$G$2:$H$11,2,FALSE)),"",VLOOKUP(A2,$G$2:$H$11,2,FALSE))
E2=IF(ISERROR(VLOOKUP(D2,$J$2:$K$11,2,FALSE)),"",VLOOKUP(D2,$J$2:$K$11,2,FALSE))


If we give Andy some codes in cell H4 like this:


Excel 2007
ABCDEFGHIJK
1NameCodesCodesDefinitionsNameCodesCodeDefinitions
2Paul3-41HouseHarry1-91House
3Harry1-92CatJames1-6-92Cat
4James1-6-93DogPaul3-43Dog
5Andy1-2-3-4-5-6-7-8-9-104FishMatt1-3-5-6-74Fish
65BallAndy1-2-3-4-5-6-7-8-9-105Ball
76Jam6Jam
87Tea7Tea
98Coffee8Coffee
109Cream9Cream
1110Toy10Toy
12
Sheet1
Cell Formulas
RangeFormula
B2=IF(ISERROR(VLOOKUP(A2,$G$2:$H$11,2,FALSE)),"",VLOOKUP(A2,$G$2:$H$11,2,FALSE))
E2=IF(ISERROR(VLOOKUP(D2,$J$2:$K$11,2,FALSE)),"",VLOOKUP(D2,$J$2:$K$11,2,FALSE))


And, if we run the macro again, we get this:


Excel 2007
ABCDEFGHIJK
1NameCodesCodesDefinitionsNameCodesCodeDefinitions
2Paul3-41HouseHarry1-91House
3Harry1-92CatJames1-6-92Cat
4James1-6-93DogPaul3-43Dog
5Andy1-2-3-4-5-6-7-8-9-104FishMatt1-3-5-6-74Fish
65BallAndy1-2-3-4-5-6-7-8-9-105Ball
76Jam6Jam
87Tea7Tea
98Coffee8Coffee
109Cream9Cream
1110Toy10Toy
12
Sheet1
Cell Formulas
RangeFormula
B2=IF(ISERROR(VLOOKUP(A2,$G$2:$H$11,2,FALSE)),"",VLOOKUP(A2,$G$2:$H$11,2,FALSE))
E2=IF(ISERROR(VLOOKUP(D2,$J$2:$K$11,2,FALSE)),"",VLOOKUP(D2,$J$2:$K$11,2,FALSE))


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code: 
Sub ExtractUniqueCodes()
' hiker95, 02/09/2015, ME834647
Dim d As Object, b As Variant, i As Long, s, j As Long
Set d = CreateObject("Scripting.Dictionary")
b = Range("B2:B8")
Range("D2:D11").ClearContents
For i = LBound(b, 1) To UBound(b, 1)
  If InStr(b(i, 1), "-") = 0 And b(i, 1) <> "" And b(i, 1) <> 0 Then
    If Not d.Exists(b(i, 1)) Then
      d(b(i, 1)) = 1
    End If
  ElseIf InStr(b(i, 1), "-") > 0 Then
    s = Split(b(i, 1), "-")
    For j = LBound(s) To UBound(s)
      If Not d.Exists(s(j)) Then
        d(s(j)) = 1
      End If
    Next j
  End If
Next i
Range("D2:D11").ClearContents
Range("D2").Resize(d.Count) = Application.Transpose(d.Keys)
Range("D2:D" & d.Count + 1).Sort key1:=Range("D2"), order1:=1
Columns(5).AutoFit
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the ExtractUniqueCodes macro.
 
Upvote 0
DHChemist,

The formula in cell B2 should be copied down to B8:

=IF(ISERROR(VLOOKUP(A2,$G$2:$H$11,2,FALSE)),"",VLOOKUP(A2,$G$2:$H$11,2,FALSE))



The formula in cell E2 should be copied down to E11:

=IF(ISERROR(VLOOKUP(D2,$J$2:$K$11,2,FALSE)),"",VLOOKUP(D2,$J$2:$K$11,2,FALSE))
 
Upvote 0
Many thanks for the solution hiker95, it works perfectly.

My only problem is when I attempt to adapt the code to work on a different set of cells, rather than B2:B8 and D2:D11. For example, if I wanted to apply it to B31:B41 and D31:D41, I have tried:

Code: 
Sub ExtractUniqueCodes()
' hiker95, 02/09/2015, ME834647
Dim d As Object, b As Variant, i As Long, s, j As Long
Set d = CreateObject("Scripting.Dictionary")
b = Range("[B]B31:B41[/B]")
Range("[B]D31:D41[/B]").ClearContents
For i = LBound(b, 1) To UBound(b, 1)
  If InStr(b(i, 1), "-") = 0 And b(i, 1) <> "" And b(i, 1) <> 0 Then
    If Not d.Exists(b(i, 1)) Then
      d(b(i, 1)) = 1
    End If
  ElseIf InStr(b(i, 1), "-") > 0 Then
    s = Split(b(i, 1), "-")
    For j = LBound(s) To UBound(s)
      If Not d.Exists(s(j)) Then
        d(s(j)) = 1
      End If
    Next j
  End If
Next i
Range("[B]D31:D41[/B]").ClearContents
Range("D[B]31[/B]").Resize(d.Count) = Application.Transpose(d.Keys)
Range("[B]D31:D[/B]" & d.Count + 1).Sort key1:=Range("[B]D31[/B]"), order1:=1
Columns(5).AutoFit
End Sub

With my changes highlighted in bold. I'm finding that it doesn't populate D31, and occasionally pulls in values from elsewhere in my spreadsheet, rather than the desired B31:B41 range.
I suspect I'm missing some change that must be made when trying to transpose the code, so if you (or anyone else) could point out where I'm going wrong, it'd be greatly appreciated.
 
Last edited:
Upvote 0
DHChemist,

rather than B2:B8 and D2:D11. For example, if I wanted to apply it to B31:B41 and D31:D41
Click to expand...

So that I can get it right with your new request, can you supply new screenshots of before, and, after?
 
Last edited:
Upvote 0
DHChemist,

If I understand you correctly, here is a new macro for you to consider.

Sample raw data:


Excel 2007
ABCDEFGHIJK
29
30NameCodesCodesDefinitionsNameCodesCodeDefinitions
31Paul3-4 Harry1-91House
32Harry1-9James1-6-92Cat
33James1-6-9Paul3-43Dog
34Andy1-2-3-4-5-6-7-8-9-10Matt1-3-5-6-74Fish
35Andy1-2-3-4-5-6-7-8-9-105Ball
366Jam
37 7Tea
388Coffee
399Cream
4010Toy
41
42
Sheet1
Cell Formulas
RangeFormula
B31=IF(ISERROR(VLOOKUP(A31,$G$31:$H$40,2,FALSE)),"",VLOOKUP(A31,$G$31:$H$40,2,FALSE))
B37=IF(ISERROR(VLOOKUP(A37,$G$31:$H$40,2,FALSE)),"",VLOOKUP(A37,$G$31:$H$40,2,FALSE))
E31=IF(ISERROR(VLOOKUP(D31,$J$31:$K$40,2,FALSE)),"",VLOOKUP(D31,$J$31:$K$40,2,FALSE))


After the new macro:


Excel 2007
ABCDEFGHIJK
29
30NameCodesCodesDefinitionsNameCodesCodeDefinitions
31Paul3-41HouseHarry1-91House
32Harry1-92CatJames1-6-92Cat
33James1-6-93DogPaul3-43Dog
34Andy1-2-3-4-5-6-7-8-9-104FishMatt1-3-5-6-74Fish
355BallAndy1-2-3-4-5-6-7-8-9-105Ball
366Jam6Jam
37 7Tea7Tea
388Coffee8Coffee
399Cream9Cream
4010Toy10Toy
41
42
Sheet1
Cell Formulas
RangeFormula
B31=IF(ISERROR(VLOOKUP(A31,$G$31:$H$40,2,FALSE)),"",VLOOKUP(A31,$G$31:$H$40,2,FALSE))
B37=IF(ISERROR(VLOOKUP(A37,$G$31:$H$40,2,FALSE)),"",VLOOKUP(A37,$G$31:$H$40,2,FALSE))
E31=IF(ISERROR(VLOOKUP(D31,$J$31:$K$40,2,FALSE)),"",VLOOKUP(D31,$J$31:$K$40,2,FALSE))


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Code: 
Sub ExtractUniqueCodesV2()
' hiker95, 02/10/2015, ME834647
Dim d As Object, b As Variant, i As Long, s, j As Long
Set d = CreateObject("Scripting.Dictionary")
b = Range("B31:B41")
Range("D31:D41").ClearContents
For i = LBound(b, 1) To UBound(b, 1)
  If InStr(b(i, 1), "-") = 0 And b(i, 1) <> "" And b(i, 1) <> 0 Then
    If Not d.Exists(b(i, 1)) Then
      d(b(i, 1)) = 1
    End If
  ElseIf InStr(b(i, 1), "-") > 0 Then
    s = Split(b(i, 1), "-")
    For j = LBound(s) To UBound(s)
      If Not d.Exists(s(j)) Then
        d(s(j)) = 1
      End If
    Next j
  End If
Next i
Range("D31:D41").ClearContents
Range("D31").Resize(d.Count) = Application.Transpose(d.Keys)
Range("D31:D" & 31 + d.Count + 1).Sort key1:=Range("D31"), order1:=1
Columns(5).AutoFit
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the ExtractUniqueCodesV2 macro.
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Solved
Pivot Table counting comma separated values filtered by Date
Replies
3
Views
187
Fluff
Fluff
J
  • Question
If column B is empty and column C contains a value, show column A (also using Indirect to draw data from another workbook)
Replies
1
Views
103
Fluff
Fluff
S
  • Question
Auto-population based on number
Replies
1
Views
292
Peter_SSs
Peter_SSs
B
  • Solved
Populating combobox from table based on the header selected in another combobox (some data on different sheets)
Replies
7
Views
324
BWL388
B
W
  • Question
Table Lookup auto suggestions/return
Replies
0
Views
97
Wolfmanfafa
W

Forum statistics

Threads
1,216,126
Messages
6,129,005
Members
449,480
Latest member
yesitisasport

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