Solved

Listing out of a listing

Posted on 2011-09-06
9
275 Views
Last Modified: 2012-05-12
Dear Experts,
I have a list of values in $A:$A and a corresponding list of values in let's say $B:$B. At another sheet I would like to get a list of all those values which meet a certain criteria...something like countif..

For example in a and in b I have the following:
NIK FC
KAG IT
AMB HC
CAC FC
MGS HC
PPY IT
TNM CEO
YAG SEC
etc..

in sheet2 I want to have now as a result in a column A called FC the following:
FC
NIK
CAC

and in the column B called HC the following:
HC
AMB
MGS

thanks
0
Comment
Question by:Petersburg1
  • 4
  • 4
9 Comments
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 36493958
Hello,

you can use a pivot table to group the values by their respective categories. See attached.

cheers, teylyn


Book1.xlsx
0
 

Author Comment

by:Petersburg1
ID: 36494461
Hi Teylyn,
I do not want a pivot table, lookin for a formula which allows me to realize that, if possible.
Where to find/switch on this pivot table?
thanks
Nils
0
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 36494548
Hello,

If you don't want to use a pivot table, why do you want to know how to create one? :-)

Here's how to get started with pivot tables: http://peltiertech.com/Excel/Pivots/pivotstart.htm

To do what you describe without a pivot table is rather complex:

First, you would have to work out the unique values of column B. This would be either manually or by using the Unique switch of the Advanced Filter, then transposing the result to a row (from the column source). This will not be dynamic. -- Compare with:  A pivot table will update dynamically upon refresh.

Then you would need to figure out a formula to return the 1st, 2nd, 3rd, etc. match for the desired column header. Not a mean feat with formulas. Refer to http://www.ozgrid.com/Excel/find-nth.htm for a few approaches, neither for the faint-hearted.

You'd also need to know how many items will possibly be returned for each column, so you can cater for the formula to be copied down a sufficient number of rows.

In short: Excel does not come with a simple, native, two or three click solution to present the result in your desired layout.

The two or three click solution can be achieved by a Pivot Table, as I described in my previous post.

You may want to take a step or two back and explain the bigger picture. There may be approaches that differ from your conception of a solution, approaches which may be easier to implement.

If not, I'm afraid the Pivot Table is the most dynamic and flexible, AND easy to create approach.

cheers, teylyn
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Author Comment

by:Petersburg1
ID: 36494826
Ok, thanks a lot.
Here the bigger already simplified picture and I guest this should be possible.

In an Excel file A I have a list of employee abbreviations like NIK, KAG, OVS etc. and in the same excel file also a column which is listing their position abbreviation like HC, FC, CEO etc.

In another excel file which is linked with the first one I have several columns for the different job titles like HC and another one with FC and so on and I would like here to get a sorted listing of employees.
All FC employees shall be listed in the FC column and so on.
I know that we never ever will have more than 50 FCs so I could run down a formula which either gives me the value or "" empty space.....
I think it should be something like vlookup or sumproduct or countif and give me as result not a number but the abbreviation and in this way it should result into a listing?
thanks
Nils
0
 

Author Comment

by:Petersburg1
ID: 36494869
Hi again,
just looked that interesting link: http://www.ozgrid.com/Excel/find-nth.htm but although nice still not helping me to come up with the approach.
Helping columns are fine with me too.

I simply need a dynamic list of the FCs in Excel file B for field validation which is coming from Excel A where we have a list of all our employees which is constantly kept up to date....so working with Excel file B would give me at any time an updated list of employee abbreviations which are FCs...(Financial controllers)...

thanks


0
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 36494951
Hello,

So there are different FILES involved, not just different sheets in the same file? That does not make the situation any easier.

Even if you use words like "just" and "simply" and "dynamic" in your question does not mean that the result can be easily achieved with a simple formula. The Ozgrid link posted above has all the information required to look up and return the nth occurrence of a value in a given column, providing a solution for both a formula approach and with VBA as an alternative.

If you've tried the Ozgrid methods and they do not work for you, please post the file(s) with the Ozgrid formula or VBA applied to your source data, so we can have a look at what is going wrong.

cheers, teylyn

0
 
LVL 50

Accepted Solution

by:
Ingeborg Hawighorst earned 250 total points
ID: 36495023
See attached file

formula in E2, copy down and across.

=IFERROR(INDEX($A$2:$A$17,MATCH(E$1&TEXT(ROW()-1,"00"),$C$2:$C$17,0)),"")

E1 to I1 hold the list of unique strings in column B. Column C is a helper cell, as described in the Ozgrid link above. Instead of a Vlookup, this solution uses Index/Match, since the lookup value is to the right of the lookup return value.

cheers, teylyn
Book2.xlsx
0
 
LVL 6

Assisted Solution

by:TinTombStone
TinTombStone earned 250 total points
ID: 36495730
This lot seems to work.  At least on your example!

Have attached an example workbook with the code working with your data MatchValues.xls


Sub MatchVals()

Dim rng1 As Range
Dim rng2 As Range
Dim rowCount As Long
Dim arr1() As Variant
Dim arr2() As Variant
Dim counter As Integer, counter2 As Integer
Dim colInc As Integer, rowInc As Integer
colInc = 1
rowInc = 1
    
    Sheets("Sheet1").Activate
    rowCount = Range("A1").CurrentRegion.Rows.Count
    Set rng1 = Range(Cells(1, 1), Cells(rowCount, 2))
    Set rng2 = Range(Cells(1, 2), Cells(rowCount, 2))
    
    arr1 = rng1
    arr2 = rng2
    
    SortArr arr2
    
    Sheets("Sheet2").Activate
    Range("A1").CurrentRegion.Clear
    For counter = 1 To UBound(arr2, 1) - 1
        If arr2(counter, 1) <> arr2(counter + 1, 1) Then
            Cells(1, colInc).Value = arr2(counter, 1)
            colInc = colInc + 1
        End If
    Next
        Cells(1, colInc).Value = arr2(UBound(arr2, 1), 1)
    Set rng2 = Range(Cells(1, 1), Cells(1, Cells(1, 1).CurrentRegion.Columns.Count))
    arr2 = rng2
    
    For counter = 1 To UBound(arr2, 2)
        For counter2 = 1 To UBound(arr1, 1)
        
            If arr1(counter2, 2) = arr2(1, counter) Then
                Cells(rowInc + 1, counter).Value = arr1(counter2, 1)
                rowInc = rowInc + 1
            End If
        Next counter2
        rowInc = 1
    Next counter
    
    
End Sub


Sub SortArr(ByRef arr As Variant)
Dim tempVal As Variant
Dim counter As Integer
    For counter = 1 To UBound(arr, 1) - 1
        If arr(counter, 1) > arr(counter + 1, 1) Then
            tempVal = arr(counter, 1)
            arr(counter, 1) = arr(counter + 1, 1)
            arr(counter + 1, 1) = tempVal
            SortArr arr
        End If
    Next
End Sub

Open in new window

0
 

Author Comment

by:Petersburg1
ID: 36497112
Hi,
thanks a lot. I found a workable solution but like yours even more!
Also the code is very nice as not helping columns at all are needed
thanks
Nils

Listing-out-of-Listing.xlsx
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
VBA: compare column rows by rows based on a config sheet v3 11 28
Subtraction 4 15
Alphabetical Order for Letters 2 21
VLOOKUP 6 16
Many companies are making the switch from Microsoft to Google Apps (https://www.google.com/work/apps/business/). Use this article to learn more about what Google Apps has to offer and to help if you’re planning on migrating to Google Apps. It is …
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

785 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