Unique list out of non-unique row

T

tabibito

New Member
Joined
Jan 28, 2014
Messages
7
Hello everyone!

mrexcel has been my top resource when it comes to Excel-related questions. Great to be a member now.

What follows is a tricky combination (for me, that is) of
- starting from several rows named "A", "B", ... with blank cells, zeroes and values larger than zero in columns named "1", "1.5", ...
- transposing them into a continuous vertical list of "row name - column name" pairs (e.g. "A" "1")
- leaving out all the blank cells and zeroes.


[TABLE="class: grid, width: 507, align: center"]
<tbody>[TR]
[TD]　[/TD]
[TD]1
[/TD]
[TD]1,5[/TD]
[TD]2[/TD]
[TD]2,5[/TD]
[TD]3[/TD]
[TD]3,5[/TD]
[TD]4[/TD]
[TD]4,5[/TD]
[TD]5[/TD]
[TD]5.5[/TD]
[TD][/TD]
[TD][/TD]
[TD]A
[/TD]
[TD]2.5[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]　[/TD]
[TD]4[/TD]
[TD]　[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]A[/TD]
[TD]3[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD][/TD]
[TD][/TD]
[TD]A[/TD]
[TD]4[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD][/TD]
[TD][/TD]
[TD]A[/TD]
[TD]5.5[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD][/TD]
[TD][/TD]
[TD]B[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD][/TD]
[TD][/TD]
[TD]B[/TD]
[TD]1.5[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD][/TD]
[TD][/TD]
[TD]B[/TD]
[TD]3[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD][/TD]
[TD][/TD]
[TD]B[/TD]
[TD]3.5[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD][/TD]
[TD][/TD]
[TD]B[/TD]
[TD]4.5[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD][/TD]
[TD][/TD]
[TD]C[/TD]
[TD]2.5[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD][/TD]
[TD][/TD]
[TD]C[/TD]
[TD]3
[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD][/TD]
[TD][/TD]
[TD]D[/TD]
[TD]1.5[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]　[/TD]
[TD]　[/TD]
[TD]　
[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD][/TD]
[TD][/TD]
[TD]D[/TD]
[TD]2.5[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]　
[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]　[/TD]
[TD][/TD]
[TD][/TD]
[TD]D[/TD]
[TD]3[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD][/TD]
[TD][/TD]
[TD]D[/TD]
[TD]4[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD]　[/TD]
[TD][/TD]
[TD][/TD]
[TD]D[/TD]
[TD]4.5[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD][/TD]
[TD][/TD]
[TD]D[/TD]
[TD]5[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[/TR]
</tbody>[/TABLE]


What I've tried so far:
(1) Created a helper table with the full range of 1 , 1.5 , 2 , ... , 5 , 5.5, displaying "" for all blanks or zeroes in the upper left chart.
(2) From that, tried to create another helper table displaying only the unique values of the helper table created in (1). This did not work out because the array formula below does not accept formula ""s:
=IFERROR(INDEX($helpertablerange,SMALL(IF(ISBLANK($helpertablerange),"",COLUMN($helpertablerange)-MIN(COLUMN($helpertablerange))+1),COLUMN(C1))),"")
CTRL+SHIFT+ENTER
(3) From that, planned on using INDEX/MATCH combined with the TRANSPOSE function to create the unique list of "ROW LETTER" x "COLUMN NUMBER" x "VALUE" pairs.

I assume this is only one hint for a possible solution to the problem and certainly not the one to the most efficient one.

Any hints leading me towards solving this quiz would be greatly appreciated.

Thank you in advance.

Tabibito
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Sort by date Sort by votes
tabibito,

Welcome to the MrExcel forum.

What version of Excel and Windows are you using?


How about a macro solution?


Sample raw data:


Excel 2007
ABCDEFGHIJKLMNOP
111,522,533,544,555.5
2A012401
3
4
5
6B12004501
7
8
9
10C11
11
12
13
14D10230111
15
16
17.................................
18
19
Sheet1


After the macro:


Excel 2007
ABCDEFGHIJKLMNOP
111,522,533,544,555.5A2.51
2A012401A32
3A44
4A5.51
5B11
6B12004501B1.52
7B34
8B3.55
9B4.51
10C11C2.51
11C31
12D1.51
13D2.52
14D10230111D33
15D41
16D4.51
17.................................D51
18.........
19
Sheet1


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: 
Option Explicit
Sub ReorgData()
' hiker95, 01/28/2014, ME753744
Dim oa As Variant, lr As Long, lc As Long, d As Range, h As String
Dim a As Variant, o As Variant, c As Long
Dim i As Long, ii As Long, n As Long
Application.ScreenUpdating = False
lr = Cells(Rows.Count, 1).End(xlUp).Row
lc = Cells(1, Columns.Count).End(xlToLeft).Column
oa = Range(Cells(1, 1), Cells(lr, lc))
For Each d In Range(Cells(1, 1), Cells(1, lc))
  If InStr(d, ",") Then
    h = d
    h = Replace(h, ",", ".")
    d.NumberFormat = "0.0"
    d = h
  End If
Next d
a = Range(Cells(1, 1), Cells(lr, lc))
n = Application.CountIf(Range(Cells(2, 2), Cells(lr - 1, lc)), ">0")
ReDim o(1 To n * 2, 1 To 3)
For i = 2 To UBound(a, 1)
  If a(i, 1) <> "" Then
    For c = 2 To lc
      If a(i, c) > 0 And a(i, c) <> "..." Then
        ii = ii + 1
        o(ii, 1) = a(i, 1)
        o(ii, 2) = a(1, c)
        o(ii, 3) = a(i, c)
      ElseIf a(i, 1) = "..." Then
        ii = ii + 1
        o(ii, 1) = a(i, 1)
        o(ii, 2) = "..."
        o(ii, 3) = "..."
        GoTo Continue
      End If
    Next c
  End If
Next i
Continue:
Range("A1").Resize(UBound(oa, 1), UBound(oa, 2)) = oa
Cells(1, lc + 3).Resize(UBound(o, 1), UBound(o, 2)) = o
lr = Cells(Rows.Count, lc + 3).End(xlUp).Row
Range(Cells(1, lc + 3), Cells(lr, lc + 3)).Font.Bold = True
Columns.AutoFit
Application.ScreenUpdating = True
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 ReorgData macro.
 
Upvote 0
Dear hiker95,

Your code made my day - I'm amazed. What you gave me is exactly what I was looking for to solve this riddle.

The only reason I tried to get around Macro is that I am not confident with vba. Your macro operates smoothly, fast and the file size remains amazingly small.

I have become interested in learning more about creating macros myself now. First step will be to understand your code, and do some tests with modified versions of it.

Thanks a lot!!

tabibito


P.S.: Thanks for the keyboard shortcuts also, makes operating excel so much smoother.
 
Upvote 0
tabibito,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.
 
Upvote 0
Dear hiker95,

while your code worked perfectly on my excel 2010,
it gives me a Subscript out of range (Error 9) when trying to open it from a PC with excel 2007.

Debugging highlights the following red line:


Code: 
Option Explicit
Sub ReorgData()
' hiker95, 01/28/2014, ME753744
Dim oa As Variant, lr As Long, lc As Long, d As Range, h As String
Dim a As Variant, o As Variant, c As Long
Dim i As Long, ii As Long, n As Long
Application.ScreenUpdating = False
lr = Cells(Rows.Count, 1).End(xlUp).Row
lc = Cells(1, Columns.Count).End(xlToLeft).Column
oa = Range(Cells(1, 1), Cells(lr, lc))
For Each d In Range(Cells(1, 1), Cells(1, lc))
  If InStr(d, ",") Then
    h = d
    h = Replace(h, ",", ".")
    d.NumberFormat = "0.0"
    d = h
  End If
Next d
a = Range(Cells(1, 1), Cells(lr, lc))
n = Application.CountIf(Range(Cells(2, 2), Cells(lr - 1, lc)), ">0")
ReDim o(1 To n * 2, 1 To 3)
For i = 2 To UBound(a, 1)
  If a(i, 1) <> "" Then
    For c = 2 To lc
      If a(i, c) > 0 And a(i, c) <> "..." Then
        ii = ii + 1
        [B][COLOR=#ff0000]o(ii, 1) = a(i, 1)[/COLOR][/B]
        o(ii, 2) = a(1, c)
        o(ii, 3) = a(i, c)
      ElseIf a(i, 1) = "..." Then
        ii = ii + 1
        o(ii, 1) = a(i, 1)
        o(ii, 2) = "..."
        o(ii, 3) = "..."
        GoTo Continue
      End If
    Next c
  End If
Next i
Continue:
Range("A1").Resize(UBound(oa, 1), UBound(oa, 2)) = oa
Cells(1, lc + 3).Resize(UBound(o, 1), UBound(o, 2)) = o
lr = Cells(Rows.Count, lc + 3).End(xlUp).Row
Range(Cells(1, lc + 3), Cells(lr, lc + 3)).Font.Bold = True
Columns.AutoFit
Application.ScreenUpdating = True
End Sub

I checked a bit on the error code and found out that either it happens when
- a nonexistent collection member is referenced
- a nonexistent array element is referenced
- the number of elements is not defined

Then again, I have the feeling that we might be dealing rather with an excel 2007 compatibility issue here?

Best Regards

tabibito
 
Upvote 0
Hello hiker95,

I have no idea what happened on the excel 2007 PC in the meantime, but when I just retried executing the macro there, it worked just as perfectly as on my excel 2010 PC.
Just hope this problem is not going to come up again in the future.

Thank you anyways!
tabibito
 
Upvote 0
tabibito,

Strange, because, I am using Excel 2007, and, when I tested the macro based on your screenshot and description, it worked fine.

Can you give me your workbook for testing your actual raw data?


You can upload your workbook to Box Net,

sensitive data changed

mark the workbook for sharing
and provide us with a link to your workbook.
 
Last edited:
Upvote 0
tabibito,

Thanks for the workbook. The new data structure and cell formatting is different than your original screenshot.

New sample raw data:


Excel 2007
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACAD
1Start6060.56161.56262.56363.56464.56565.56666.56767.56869.58080.58181.58282.5
21234-1231151
31233-122111
41232-1212
51222-122111
61221-1211113211
71212-111111
81211-1231
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
Sheet1


After the new macro:


Excel 2007
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACAD
1Start6060.56161.56262.56363.56464.56565.56666.56767.56869.58080.58181.58282.51234-12360.51
21234-12311511234-12362.51
31233-1221111234-12364.55
41232-12121234-12366.51
51222-1221111233-12261.51
61221-12111132111233-12263.51
71212-1111111233-122661
81211-12311232-12181.52
91222-12260.51
101222-12261.51
111222-122811
121221-121611
131221-121631
141221-12164.51
151221-121653
161221-12165.52
171221-121661
181221-121821
191212-11163.51
201212-11169.51
211212-111811
221211-12367.51
23
Sheet1


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: 
Option Explicit
Sub ReorgDataV2()
' hiker95, 02/06/2014, ME753744
Dim lr As Long, lc As Long, d As Range, h As String
Dim a As Variant, o As Variant, c As Long
Dim i As Long, ii As Long, n As Long
Application.ScreenUpdating = False
lr = Cells(Rows.Count, 1).End(xlUp).Row
lc = Cells(1, Columns.Count).End(xlToLeft).Column
a = Range(Cells(1, 1), Cells(lr, lc))
n = Application.CountIf(Range(Cells(2, 2), Cells(lr - 1, lc)), ">0")
ReDim o(1 To n * 2, 1 To 3)
For i = 2 To UBound(a, 1)
  If a(i, 1) <> "" Then
    For c = 2 To lc
      If a(i, c) > "" And a(i, c) <> "..." Then
        ii = ii + 1
        o(ii, 1) = a(i, 1)
        o(ii, 2) = a(1, c)
        o(ii, 3) = a(i, c)
      ElseIf a(i, 1) = "..." Then
        ii = ii + 1
        o(ii, 1) = a(i, 1)
        o(ii, 2) = "..."
        o(ii, 3) = "..."
        GoTo Continue
      End If
    Next c
  End If
Next i
Continue:
Cells(1, lc + 3).Resize(UBound(o, 1), UBound(o, 2)) = o
lr = Cells(Rows.Count, lc + 3).End(xlUp).Row
Range(Cells(1, lc + 3), Cells(lr, lc + 3)).Font.Bold = True
Columns.AutoFit
Application.ScreenUpdating = True
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 ReorgDataV2 macro.
 
Upvote 0
tabibito,

Thanks for the workbook. The new data structure and cell formatting is different than your original screenshots.

Sorry about the previous thread screenshots.

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: 
Option Explicit
Sub ReorgDataV2()
' hiker95, 02/06/2014, ME753744
Dim lr As Long, lc As Long, d As Range, h As String
Dim a As Variant, o As Variant, c As Long
Dim i As Long, ii As Long, n As Long
Application.ScreenUpdating = False
lr = Cells(Rows.Count, 1).End(xlUp).Row
lc = Cells(1, Columns.Count).End(xlToLeft).Column
a = Range(Cells(1, 1), Cells(lr, lc))
n = Application.CountIf(Range(Cells(2, 2), Cells(lr - 1, lc)), ">0")
ReDim o(1 To n * 2, 1 To 3)
For i = 2 To UBound(a, 1)
  If a(i, 1) <> "" Then
    For c = 2 To lc
      If a(i, c) > "" And a(i, c) <> "..." Then
        ii = ii + 1
        o(ii, 1) = a(i, 1)
        o(ii, 2) = a(1, c)
        o(ii, 3) = a(i, c)
      ElseIf a(i, 1) = "..." Then
        ii = ii + 1
        o(ii, 1) = a(i, 1)
        o(ii, 2) = "..."
        o(ii, 3) = "..."
        GoTo Continue
      End If
    Next c
  End If
Next i
Continue:
Cells(1, lc + 3).Resize(UBound(o, 1), UBound(o, 2)) = o
lr = Cells(Rows.Count, lc + 3).End(xlUp).Row
Range(Cells(1, lc + 3), Cells(lr, lc + 3)).Font.Bold = True
Columns.AutoFit
Application.ScreenUpdating = True
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 ReorgDataV2 macro.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Solved
Combinations of 3 from 16 numbers pool with unique product
2
Replies
14
Views
623
libelouli
L
M
  • Solved
Sum only the unique values from one column within a range of duplicate values from another column
Replies
7
Views
146
MARZIOTULLIO
M
J
  • Solved
Counting unique values in a range/table.
Replies
1
Views
138
Cubist
Cubist
R
  • Question Question
Move row to bottom of range is cell value is zero
Replies
6
Views
270
Peter_SSs
Peter_SSs
BrettOlbrys1
  • Solved
Count Unique Values Based on Multiple Criteria
Replies
6
Views
436
BrettOlbrys1
BrettOlbrys1

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

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