Sorting lists side by side to match variables

Hello everyone,

I have a problem that I need to find a solution for.  I have a list o
all the stores with a particular trait that carry my at least one of m
items.  Then, I have a list for each item with all the stores tha
carry that particular item.  What I want to do is sort this data s
that the store numbers under each item are matched up with th
corresponding store numbers in the master store list.  I'll give you a
example of the problem.

Here is what the data looks like now:

Master	Item 1	Item 2	Item 3	Item 4
1	2	3	1	3
2	4		2	4
3			4	
4				

And here is what I would like it to look like when I'm done:

Master	Item 1	Item 2	Item 3	Item 4
1			1	
2	2		2	
3		3		3
4	4		4	4

This way I can easily -see- which stores carry which items.  I shoul
note that not all of the stores under each item are in the master lis
- these I would like to filter out.
Is there a way to do this without going through each list and matchin
it up with the master list manually?  This has become a very tim
consuming project that I am required to do over and over again wit
different traits, and I would appreciate it if someone could help me.

Thank you for your time,
Skybo

--
Message posted from http://www.ExcelForum.com

0
2/18/2004 10:31:04 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
315 Views

Similar Articles

[PageSpeed] 0

One way is to use a macro:

Option Explicit
Sub testme()

    Dim mstrRng As Range
    Dim myColRng As Range
    Dim myColArr As Variant
    Dim FirstCol As Long
    Dim LastCol As Long
    Dim iCol As Long
    Dim wks As Worksheet
    Dim iCtr As Long
    Dim NewValRng As Range
    Dim res As Variant
    
    Set wks = Worksheets("sheet1")
    
    With wks
        FirstCol = 2
        LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
        
        For iCol = FirstCol To LastCol
            If Application.CountA(.Range(.Cells(2, iCol), _
                                      .Cells(.Rows.Count, iCol))) = 0 Then
                'do nothing
            Else
            
                Set mstrRng = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp))
                
                Set myColRng = .Range(.Cells(2, iCol), _
                                   .Cells(.Cells(.Rows.Count, iCol) _
                                              .End(xlUp).Row, iCol))

                If myColRng.Cells.Count = 1 Then
                    ReDim myColArr(1 To 1, 1 To 1)
                    myColArr(1, 1) = myColRng.Value
                Else
                    myColArr = myColRng.Value
                End If

                myColRng.ClearContents
                
                For iCtr = LBound(myColArr, 1) To UBound(myColArr, 1)
                    res = Application.Match(myColArr(iCtr, 1), mstrRng, 0)
                    If IsError(res) Then
                        Set NewValRng _
                           = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
                        With NewValRng
                            .Value = myColArr(iCtr, 1)
                            .Offset(0, iCol - 1).Value = myColArr(iCtr, 1)
                        End With
                    Else
                        mstrRng(res).Offset(0, iCol - 1).Value _
                                    = myColArr(iCtr, 1)
                    End If
                Next iCtr
            End If
        Next iCol
    End With
    
End Sub

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

"skyboy <" wrote:
> 
> Hello everyone,
> 
> I have a problem that I need to find a solution for.  I have a list of
> all the stores with a particular trait that carry my at least one of my
> items.  Then, I have a list for each item with all the stores that
> carry that particular item.  What I want to do is sort this data so
> that the store numbers under each item are matched up with the
> corresponding store numbers in the master store list.  I'll give you an
> example of the problem.
> 
> Here is what the data looks like now:
> 
> Master  Item 1  Item 2  Item 3  Item 4
> 1       2       3       1       3
> 2       4               2       4
> 3                       4
> 4
> 
> And here is what I would like it to look like when I'm done:
> 
> Master  Item 1  Item 2  Item 3  Item 4
> 1                       1
> 2       2               2
> 3               3               3
> 4       4               4       4
> 
> This way I can easily -see- which stores carry which items.  I should
> note that not all of the stores under each item are in the master list
> - these I would like to filter out.
> Is there a way to do this without going through each list and matching
> it up with the master list manually?  This has become a very time
> consuming project that I am required to do over and over again with
> different traits, and I would appreciate it if someone could help me.
> 
> Thank you for your time,
> Skyboy
> 
> ---
> Message posted from http://www.ExcelForum.com/

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
2/19/2004 4:05:21 AM
I found an easier way to accomplish this task using the vlooku
function.  Thanks anyway.

Skybo

--
Message posted from http://www.ExcelForum.com

0
3/3/2004 2:30:15 PM
Reply:

Similar Artilces:

Counting & Matching Text
Dear Experts, Please consider this & help me to sort this out: I have a Data Sheet contains 5 col namely: Emp; From; To; #OfDays; LeaveType I fill these columns on daily-basis using designed VB Form as below Emp From To #OfDays LeaveType ----------------------------------------------------------------------- Mr. A 01-Jan-06 02-Jan-06 2 Sick Mr. B 08-Jan-06 08-Jan-06 1 Casual Mr. C 12-Jan-06 16-Jan-06 5 Earned Mr. A 18-Jan-06 20-Jan-06...

sort order for payroll checks
This is a suggestion for MBS - it would be nice to have a setting where the user can select the Default Sort Order for Payroll checks so we don't have to change it every time we print checks. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com...

should variable declaration will be changed??
THis code compiles ok when i click priceqoute botton it display request id but when it comes to next it give a erreor message "invalid market no",and "invalid amount" Can anybody suggest me how to come out of this....should i change the variable type or what... void CWartz_newDlg::OnBpricequote() { // TODO: Add your control notification handler code here int pageno(0); requestid=m_arielapi.RequestPrices(sessionid,pageno); CString displaydata; displaydata.Format(_T("==>Request ID [%s]"),requestid); LOG(0,displaydata); BOOL bSuccess=FALSE; for(int n=0;n<3...

Match Account ID from combining street names and numbers
In one sheet, have a column with a street number (A): 115 in Column B, have a street name: Northwest. In workbook 2, have an additional street number column(C), and a street name column(D), and a 5th column with an account number, column (E) I need to find the account number in workbook 2 for a combination of street number,and street name found on the given row from the first workbook. There can be many street numbers on the same street, but only the combination of the desired number and called for street should return a value. Desired combinations are always found on the same row ...

List box
This is probably very simple. I have a list box (using control toolbox) in a work sheet and I want to specify that every time the workbook is opened that the first item on the list box is always selected Thanks Brian Activesheet.Listbox1.ListIndex = 0 -- HTH RP (remove nothere from the email address if mailing direct) "Brian" <Brian@discussions.microsoft.com> wrote in message news:32B7340F-05C9-47B5-B275-3FCFEBB2FDE6@microsoft.com... > This is probably very simple. > > I have a list box (using control toolbox) in a work sheet and I want to > specify ...

How To "See Everything in a Single List"
I would like to be able to see everything in all my email folders (storage folders and EVERYTHING in folders underneath the pop mail server that I use) in a single list that I can sort by date (or whatever). This must be easily doable, but how do I do this? Thanks. dave Use a Quick View called "All e-mail" To turn on Quick view, click on View - Layout - Folder Pane - Show Quick views To select "All e-mail", right-click on QuickViews - Select quick views - All e-mail -- Dave N. MS-MVP (Mail) Windows 7 Ultimate http://download.live.com/w...

Sort months in calendar order
From my Invoices table, I have a query that groups our charges together by month and then shows each month with its respective total revenue. I use this query as the record source for a report, but the months are listed in the Detail section in alphabetical order rather than calendar order i.e. December November October Whereas of course I want to see October November December (Interestingly, the chart that I have placed in the report does show the months in correct calendar order). Thanks for your advice! CW It would help if you provided use with something about your fields and...

Unable to Sort Multiple Columns
Good day all! I've seen this posted in a million other places, but I wasn't able to get my question fully answered. I'm creating a simple sheet to list common TCP/IP service names and their appropriate port numbers. The first column is a description of the service name (which is 4 cells wide) and then I have two sets of 2 merged cells which contain the UDP & TCP port ranges respectively. I want to sort the entire list by the TCP port numbers, but can't because Excel says that the "merged cells must be identically sized." Everyone's solution to this proble...

Sorting Cells with Conditional Formats 04-27-10
Is it possible to sort a spreadsheet containing conditional formats without destroying the formats? DOUG ECKERT 27 Apr 10 Yes, as long as the Sort doesn't change the value of the "condition". DOUG wrote: > Is it possible to sort a spreadsheet containing conditional formats without > destroying the formats? > > DOUG ECKERT > 27 Apr 10 Cell borders and CF conditions do not move with sorted cell values. Forcing the CF conditions and borders to move with the cell values can be done using VBA code. However, if the CF conditions depend on the ro...

sorting date " MMM-YY" (Jan-05)
Hi, I have a spreadsheets with ticket numbers and the date it was submitted. It is in medium date format (3/18/2005). I have used this formula: =text(A2,"MMM-YY") to conver it to Mar-05. now i have a column with MMMYYY date, however, it will not let me sort it. It is sorting alphabetically, and not in the order of the date. so I have Feb-04,Feb-05, Jan-04,Jan-05 and so on. How can I sort this by month then year? Thank you! It is sorting alphabetically because the data is in TEXT format. In order to sort by date, you'll need to have the data formatted in some kind o...

Shown all record in a list box from combobox.
Hi all, I have a listbox that can be filtered from a combo box. Now I want the combobox has an option ( called ALL ), so when the combobox value is ALL,all the data in listbox is shown. Below is the SQL of my lisbox records source : SELECT [Model specification_tbl].Model FROM [Model specification_tbl] WHERE ((([Model specification_tbl].Model) Like [Forms]![Start input data_frm]![cbo_fourdigitsmodel] & "*")) ORDER BY [Model specification_tbl].Model; Thank's for your help. hi shiro, shiro wrote: > Hi all, > I have a listbox that can be filtered from a combo box....

Cannot Scroll Global Address List
Using Outlook 2000, I cannot scroll with the wheel mouse the Global Address List or All Users under All Address Lists. I can scroll every where else in Outlook. ???? Ted ...

Strange Characters in the Accounts List Window
Hello: A client of ours is on Great Plains 8.0 with service pack 1a. He likes to make use of the "Accounts" window from the Financial Lists section of the Navigation Pane. Intermittently--certainly not all of the time--he will click on this list and some of the account descriptions will be "fuzzy" or, at best, the characters are just plain strange looking. The only thing that is different about the client's GP app is that I used Modifier to increase the width of the Account number column in the chart of accounts lookup window. I had to do this because the clie...

Match result is sometimes #N/A
Hi, I'm using the following formula. The problem is that sometimes my value isn't found and I'm not getting a blank but rather #N/A. =IF(MATCH("POS*",B$83:B$782,0),"Please be advised the POS LOBs have been converted to PPO LOBs", "") 1. Is there a fix to my formula? or 2. In the cell that results #N/A - I could set a conditional format to make a white background and white font. However, when I try that - it's not working either. Cell format = #N/A doesn't do what I want. Try this, Sharon: =IF(ISNA(MATCH("POS*",B$83:B...

Pick From List #5
I right click on cell, find "Pick from List" option, but cannot seem to use it The list should show entries from other cells in the adjacent range, in the same column. Another way to display a list in a cell is to use Data Validation. There are instructions in Excel's Help, and here: http://www.contextures.com/xlDataVal01.html Barry wrote: > I right click on cell, find "Pick from List" option, but cannot seem to use it -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html ...

Find/Filter multiple non unique list
I'm trying to figure out the quickest way to search a column for a lis of values and the answer may not always be exact. For example, within a column, I want to find if there are any of a lis companies (ABC, IBM, Sears, XYZ, etc.). Those companies may be liste there, but not exact. For example, there may be ABC Inc. or AB Corporation. This is a routine search that is needed to be done. When I use vlookup, I can only do exact matches. Any ideas on how t do this quickly? It would save a lot of people a lot of time. Thank you for any help -- Message posted from http://www.ExcelForum...

sorting multiple columns
I have multiple addresses on a work sheet. I need to have all the same zip code # together. How do I sort them by zip code so right name and address ends up with right zip code Highlight the range you want to sort before starting it. Regards, Fred "arbe" <arbe@discussions.microsoft.com> wrote in message news:56FFD696-D31F-4A99-AE8E-941C5C18D012@microsoft.com... >I have multiple addresses on a work sheet. I need to have all the same zip > code # together. How do I sort them by zip code so right name and address > ends up with right zip code Pre-se...

Microsoft Outlook Password List
I am trying to have my outlook remember my user ID and Password once I log on to my ISP provider, but despite multiple attempts, it is not remembering. I have to fill in the information manually and eventhough the "remember my password command is checked" it does not remember it once outlook exists. I have gone to tools, view or modify existing accounts and done the password routine insertion a lot of times, without any success. I even went to the internet options, and enabled the "allow windows to remember commonly used passwords" What could be wrong? ...

Novice trying to sort the values on a radar graph
Hi, I need the values on a radar grapgh to show as 0, 3, 6, and 9 as the outside of the 'web'. However the values in my data only go up to 5, how can I make the graph show up to 9 and only show the 3 radiating lines I want i.e. 3, 6, & 9? really new to this, so feel free to treat like an idiot! Good question. Here's what you do: * Slide the cursor over the rib of the radar graph until you see a little box open up saying "format axis". * Right-click on the axis. * A little dialog box opens up, and you should click the 'format axis' option. * A bigge...

How do I use the autofilter to sort a list
How do I use the autofilter to sort a list You don't. Autofilter is used to filter a list. Data - Sort is used to sort a list. HTH Otto "cookie" <cookie@discussions.microsoft.com> wrote in message news:5A04C6E7-C110-4260-8EC2-05DDE94D0BEF@microsoft.com... > How do I use the autofilter to sort a list Depends on the version of excel you're using. If you apply the data|filter|autofilter to the list, do you see "Sort ascending" and "sort descending" at the top of the list when you use the arrow in the header? cookie wrote: > > How ...

Regression with more than 16 variables
I noticed linest and the regrssion tool doesn't allow more than 16 variables. Does anyone know the most convenient way to do this? I currently don't have SAS. Is it possible in Access or anything else? Without being pessimistic I think you'll struggle to do this mutlivariate regression in excel. There may be a way but it will be long-winded I am sure. As a suggestion, stats websites often allow you to input data and see the output running off Javascript as an example. Try and find a website that you can cut and paste your data to and crank the analysis... SAS could do it ea...

Conditional listing & double selecting
Hi, I have Sheet1 with names on column A:A and associated department info's on B:B for each name on A:A. I want to add names to another sheets (say Sheet2) A:A column. But when I select a cell on Sheet2 A:A, I want to be able to see the unique depts list from Sheet1's B:B column. On selecting the dept. I want to see the list of names from that dept "only". Finally, I want to be able to select from the names of Sheet1's A:A. Is this possible? TIA Hi as a starting point: http://www.contextures.com/xlDataVal02.html and http://www.xldynamic.com/source/xld.Dropdowns.html -...

Sorting Records using the Double click event
I know this should be simple but can't seem to get it to work. I have 2 fields in a form "Surname" & "FirstName" all I want to do is use the sort on either field if I double click that particular field. I have tried using the runCommand record sort but get a Varible not defined error. Thanks Les Les You've described "what" you have tried, but not "how". Post the code you are trying, it might provide the newsgroup readers a few more clues... -- Regards Jeff Boyce Microsoft Office/Access MVP http://mvp.support.microsoft.com/ Micr...

Distribution List Restrictions Exchange 2003
I have some distribution lists setup in my Exchange 2003 organization and have setup restrictions so that these DL's only receive messages from a specific group of people. This works fine if you use Outlook to edit email messages. If you set Word as your email editor it seems that these restrictions are ignored. I removed myself from the group that is able to send to these DLs and I sent a message with the standard email editor and got an NDR back. With Word as my email editor the message is delivered. Has anyone else seen this behavior? If so, can anything be done about it? I can&...

Sorting the pivot table field list
How do I sort the pivot table field list? It looks like the soft order is always the order of the data source. The related radio-buttons in the pivot-table-options are greyed. ("A to Z" and "sort by data source"). I need the "A" to "Z" order to realize translations. Pleas notice! I do not need to sort the pivot data! Hi Move the Page field to the Row area temporarily Perform the Sort that you want Move the field back to the page area -- Regards Roger Govier diewue wrote: > How do I sort the pivot table field list? > ...