Searching In a Column

Please help me with a search function for a column. Given data in the
Level and Part columns below, I am trying to populate the Parent
column with the parent part number for each Part in a row. The
parent/child relationship is designated by the Level column entry,
where, for example, all of the Level 2 parts under a given Level 1
part have that Level 1 part as a parent. With nested IF functions, I
can easily march down the data as long as the Level number is
increasing or the Level returns to a value of 1. But as shown by the
"???" in the Parent column below, I do not have a way to easily and
systematically note that the parent for the Level 2 Part 090-806-00 is
really the first Level 1 part above that, Part 032-263-00. There is no
way to know ahead of time how many part of levels might be between a
part and its parent. Any suggestions?


Parent	        Level	Part
(Top Level)	0	032-102-00
032-102-00	1	032-263-00
032-263-00	2	C
032-263-00	2	033-151-00
033-151-00	3	28D33151
???	        2	090-806-00
???	        2	091-449-00
???	        2	091-965-00
???	        2	091-966-00
???	        2	090-221-00
???	        2	470-163-13
???	        2	470-023-67
???	        2	470-207-49
???	        2	28C32263
032-102-00	1	094-018-00
0
kc_cmc (2)
4/14/2004 5:47:33 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
294 Views

Similar Articles

[PageSpeed] 19

Hi
if your parent is col. A, level col. B and the description col. C and
the data starts in row 2 (row 1 is a heading row) then enter the
following array formula in A3 (A2 is the top level). Enter this formula
with CTRL+sHIFT+ENTER:
=INDEX($C$1:$C$100,MAX(IF($B$2:$B2=B3-1,ROW($B$2:$B2))))

copy this formula down for all rows.

--
Regards
Frank Kabel
Frankfurt, Germany


KC wrote:
> Please help me with a search function for a column. Given data in the
> Level and Part columns below, I am trying to populate the Parent
> column with the parent part number for each Part in a row. The
> parent/child relationship is designated by the Level column entry,
> where, for example, all of the Level 2 parts under a given Level 1
> part have that Level 1 part as a parent. With nested IF functions, I
> can easily march down the data as long as the Level number is
> increasing or the Level returns to a value of 1. But as shown by the
> "???" in the Parent column below, I do not have a way to easily and
> systematically note that the parent for the Level 2 Part 090-806-00
is
> really the first Level 1 part above that, Part 032-263-00. There is
no
> way to know ahead of time how many part of levels might be between a
> part and its parent. Any suggestions?
>
>
> Parent         Level Part
> (Top Level) 0 032-102-00
> 032-102-00 1 032-263-00
> 032-263-00 2 C
> 032-263-00 2 033-151-00
> 033-151-00 3 28D33151
> ???         2 090-806-00
> ???         2 091-449-00
> ???         2 091-965-00
> ???         2 091-966-00
> ???         2 090-221-00
> ???         2 470-163-13
> ???         2 470-023-67
> ???         2 470-207-49
> ???         2 28C32263
> 032-102-00 1 094-018-00

0
frank.kabel (11126)
4/14/2004 9:55:59 PM
Thanks for the suggestion, but I cannot get it to work. Can I send a
file except to try?
0
kc_cmc (2)
4/15/2004 1:49:34 PM
Hi
go ahead:
frank[dot]kabel[at]freenet[dot]de

But prior to this: What is the problem you encounter (note: you have to
enter this as array formula)

--
Regards
Frank Kabel
Frankfurt, Germany


KC wrote:
> Thanks for the suggestion, but I cannot get it to work. Can I send a
> file except to try?

0
frank.kabel (11126)
4/15/2004 1:54:39 PM
Reply:

Similar Artilces:

Column bar chart with y secondary axis
Hi! I want to dysplay on one chart the number of sales and the sales amount, of several products. Since the number of sales is very different from the amount, i display the amount on a secondary axis. What i can't do is to avoid both series to overlap. All i can do is to display one series thinner than the other, but i can't display the columns separatted from each other. Jon Peltier has instructions for this on his web site: http://www.geocities.com/jonpeltier/Excel/Charts/format.html#Col2Axes Pmxgs wrote: > I want to dysplay on one chart the number of sales and the...

How to add balance column on retirement acct?
I have a retirement account in Money 2004 Small Business. The columns on the Investment Transaction are: Date, Investment, Activity, Quantity, Price, and Total. I'd like to add a balance column to get a running balance. Is such a column available? Thanks, Brett In microsoft.public.money, Brett Romero wrote: >I have a retirement account in Money 2004 Small Business. The columns on >the Investment Transaction are: Date, Investment, Activity, Quantity, Price, >and Total. I'd like to add a balance column to get a running balance. Is >such a column available? ...

comparing columns of text (cross-searching)
I am trying to figure out a quick way to compare two columns of text for common phrases. The two columns - one of which has over 30k rows - have cells with company names. I simply need to identify the companies that are listed in both columns. However, because the same company may be written with slight textual differences in either column (i.e. "3M" versus "3-M"), I need to search based on the proximity of characters to each other (i.e. "3" and "M," in that order, within a couple of characters of each other). Ideally, I would also like tally of t...

More than 3 columns in the Order scheme
Hi, Is it possible to have more than 3 columns in the order scheme, and if so - is it possible to decide each column's width? I need to have 3 date fields displayed after each other, horizontally, with a tick box after each of the 3 date fields. I hope someone can help me with this problem. Thanks You can only have 3 columns in any given CRM Form. This needs to be set up when you create a new tab on a CRM Form. You would not be able to display 3 date fields and three checkbox fields in one line, though, as this would be 6 columns. You could place the checkboxes below each date fi...

Counting & Summing based on criteria on another column
I have a spreadsheet of estimate date that is incomplete (some cells have data, some do not yet). I need to be able to count the number of estimates (col A in the simple example below) that are for completed phases (col C)...in other words, the date in col C is in the past. I'll also need to be able sum col A for all completed phases (but this can be done in another cell). In the example below, I would expect the estimate count to be 2 and the estimate sum to be 4500. A B C 1 Estimate Actual ...

Relationships with multiple columns?
Hello, Is it possible to build a relationship that looks at more than one column for a single match? For example I have a table with staff information that has multiple logins (4) columns and another table with activity information that only has one login. I want to be able to query/report with information from both where login = login 1 or login2 or login3 or login4 but can only build a relationship that looks at one column then stops. Is there a way to make access do this? On Thu, 19 Jul 2007 14:17:58 -0000, Dustin R <dustin.russell@gmail.com> wrote: >Hello, >Is it possible to...

Show only rows with duplicate values in a column?
I've a spreadsheet with thousands of rows of people (last name, first name, address, etc.) I need to weed out duplicates, and sorting by lastname, firstname and then scanning the firstnames for duplicates that have the same last name is onerous!! Is there a way to have Excel (2003) show only rows in which there are more than one of a given value in a column? So let's say there's only one row with a value of "Jones" in the lastname, it'll now show that row but it'll show the three rows with "Smith" in the column? (Would be really cool if it showed rows ...

Can you search in queries???
Hi - I inherited this UUUUGGGLLLYYY database from someone that has over 200 macro and 150 query objects in it. He didn't do any coding. Because of that, I am finding it very hard to troubleshoot. Is there a way you can search for a table, form or query object referenced in a macro, like you could if it was written in a VBA module? Also, is there a way to identify all the queries that references a given table? I'd like to eliminate all non-essential macros and queries to clean things up but don't know a quick way to do that. Thanks so much for any help! Dan On Mon, 4 ...

Search Sub Form Help :)
Hi there, I know this question has probably been asked a million times, but for a n00b it's kinda hard to find the time at work to search similar threads, so apologies in advance! :) I take my issue is quite basic, so here's the jist: I currently have: 1 Table (data), 1 Add Form (fields are bound to the table), 1 Search Form (4 unbound fields) where by populating the fields with similar data captured in the existing table and clicking the "Search" cmd button, a query is activated and a new table pops up with the search results. I require: a SubForm in th...

display column A
Column A & B are hid in a spreadsheet. All the normal ways will not unhide them. any suggestions? Are they grayed out? If so, chances are your worksheet is protectd, and you'll have to go to Tools | Protection | Unprotect Sheet If not, try press F5 Type A1:B1 in the prompt. Click okay Then go to Format | Sheet | Unhide tj "Rick" wrote: > Column A & B are hid in a spreadsheet. All the normal ways will not unhide > them. any suggestions? Should have been Format | Column | Unhide tj "tjtjjtjt" wrote: > Are they grayed out? If so, chances ar...

case lookup fields should search all status
When in either the web or outlook client, you should be able to search all cases using the lookup field. Currently this only searchs active cases, there is not an easy way to search for resolved cases. We create and resolve hundreds of cases per day and have no easy way of searching for cases that have been resolved. ---------------- 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 sugg...

password protected document with hidden columns, columns appear when copy-pasted
Hi everyone, This is my problem i have this file which has a lot of info on it and obviously certain clients shouldn't see all the info in particular columns thus i have protected the spreadsheet and the workbook so that nobody can make changes to it, however i noticed recently that if I manually copy all of the content from existing spreadsheet and export it into a new spreadsheet voila! boom all my hidden info comes up here, keep in mind the option for unhide is greyed out too on the protected document. could someone tell me whether this is a software glitch or is there anything else p...

template for deleting columns.....
I'm trying automate the process of deleting about 70 columns from a spreadsheet...I need to find out how this can be done (I'm assuming I need to run some program to do this).....the columns I need to delete will always be the same and to do this manually is killing me...any ideas? Thanks, Jeff Try recording a macro when you select some columns and delete them. You'll see the code that works for you. If you're anything like me, you may want to hide the columns instead of deleting them (just another option). If you're new to macros, you may want to read David McRitc...

locate a column, sum specific columns to the right of that column
Tricky one here (I think): I have a drop down list of Labor Periods on a cover sheet. On a separate data sheet I have: Period 1 Period 2 ... Units Cost Hours Units Costs Hours The oldest Labor Period is the farthest to the right. I need a formula that will find the Labor Period selected from a drop down (DV) and sum the values to the right of that Labor Period. So if Labor Period 5 is selected, I want to add up the hours for Labor Periods 5,4,3,2,1. Any help would be lovely. Beric I think we need to be told about how you data is s...

Column headings #12
I am starting a new Excel spreadsheet and want to put text into the column headings. Please tell me how to accomplish this. Thanks. If you mean you want to replace the A, B, C etc that display in gray at the top of each column then: NO CAN DO. If your desire to do so is because you want your column labels to always be visible at the top of the screen, look in the Help file for info on freezing panes "Bridge" wrote: > I am starting a new Excel spreadsheet and want to put text into the column > headings. Please tell me how to accomplish this. Thanks. Actually ther...

Search media files
I have WMP 11 on Windows XP SP3. I have recently move all my music on an external hard disk. Now that I want to search the media files so that it changes the file path and I can listen to my music, it seems that there is no search media files in my Tools menu. What am I doing wrong? On Sun, 14 Mar 2010 14:22:01 -0700, newsy <newsy@discussions.microsoft.com> wrote: >I have WMP 11 on Windows XP SP3. I have recently move all my music on an >external hard disk. Now that I want to search the media files so that it >changes the file path and I can listen to my music, i...

The $ thing to lock cells at rows or columns
Hi group, I do know how manually modify the content of a cell so that when "pulling it" to autocomplete into further rows and columns one can lock either row or column. Like =$I$10 It is rather cumbersome though getting the $ sign where it must be. Once I knew a combination of keys that fixed that for me, but I have forgotten. And if one is optimistic enough to enter the $ sign in the help search function one is in for a reminder of who made this software. So I have to ask you guys and girls. Is there still a clever way to have the =$I$10, or has that been discarded with the ba...

search a text cell CONTAINING matching text in a dynamic range (not exact match)
Hi, I use the following, currently, to mark a row for exclusion =IF(AND(ISERROR(SEARCH("GENERIC",A2,1)),ISERROR(SEARCH("PSOFT",A2,1)),ISERROR(SEARCH("dnd",A2,1)),ISERROR(SEARCH("temp",A2,1)),ISERROR(SEARCH("train",A2,1)),ISERROR(SEARCH("do not delete",A2,1))),"","EXCLUDE") I'm looking for a way to use an exclusion keyword list on a separate sheet in a dynamic range so users may add to or delete exclusion keywords rather than have the 'hardcoded' exclusion keywords in the formula above And I'm ho...

selecting or copying columns conditionally
I am trying to do somthing simple (?) without unnecessary copies etc o the data, (my methods so far seems too messy) Basically What I want to do is scan through these various workbooks an worksheets copying in all columns that match a specific value (eg wher row 5 = "XYZ") More info: Using Excel 2002 and this needs to be rerunable on a daily basis t replace the manual copy/paste method in use now. I have source data in multiple workbooks (submitted by differen individuals) and I want to scan through these extracting out only thos columns that match a criteria. Unfortunately the da...

Printing two columns from one
My spreadsheet is only two columns side by side but it spams down 16 pages. I would like to be able to put 2 sets of the two columns on each page but excel wont sort information across multiple columns. Any idea how I could sort the main column but have it print two sets to conserve paper ? sorry if this doesnt make sense I wasnt sure exactly how to word it. -- SystemHack ------------------------------------------------------------------------ SystemHack's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26614 View this thread: http://www.excelforum.com/showthrea...

More Columns #2
I am using all columns through IV on one of my worksheets. Does anyone know how to get more columns? Hi stacy know way using Excel. this is the maximum! -- Regards Frank Kabel Frankfurt, Germany Stacy Haskins wrote: > I am using all columns through IV on one of my > worksheets. Does anyone know how to get more columns? As Frank said, that's the maximum cols But maybe you could try transposing your table / data ? (Excel has a lot more rows than columns) If so, try: http://tinyurl.com/2nmyy for an example on using TRANSPOSE() For a one-time Tranpose: Copy > Paste Specia...

Searching for a null value in Reports
I'm running the item movement report and trying to get a report on all items that do not have any departments associated with them. I can easily write a sql query with the store operations administrator, but would like to be able to do this within the report. Any suggestions? Paul, How about including the Department column and then sort on it. It will either be at the top or bottom depending on sort order. I would also start including departments on all items for future reference. -- * "Paul" <pbunn26@hotmail.com> wrote in message news:%2338XSRUhEHA.384@TK2MSFTN...

I need a macro that will search and replace data. TIA --
I have some data I need to manipulate with a macro and have no idea how to start. I run a PC with Windows XP I use Excel 2003 I have text data across a relatively large spread sheet. I need to run a macro that will open up a data entry or dialog box so that I can enter 2 pieces of data, text and a value. I need the macro to search for the text and place the value in the cell immediately to the right of the text. I also need it to place a single text character, X, in the cell immediately to the left of the text. When the X goes in the cell to the left, it will replace a number that is insig...

Column count / Access GridView Column after DataBind
Hi I have below GridView, which i in codebehind page databind with a dynamicly created datatable. This all works fine, but after the databind i still only have a column count of 1 although the datatable adds another 4 columns to the gridview. This represents a problem, since i would like to have the last column properties change on the fly and i cannot access the properties of any column besides the on definded below. None of the databound columns created by the DataBind method can be accessed.. How can i access these columns?? /Finn <asp:GridView ID="UdlaanG...

Searching the Dumpster for all Folders and Users
Hi Is there any way for an administrator to search all users dumpsters for deleted emails? Any advice would be much appreicated. Thanks B On Wed, 18 Apr 2007 10:04:06 +0100, "Ben" <Ben@Newsgroups.microsoft.com> wrote: >Hi > >Is there any way for an administrator to search all users dumpsters for >deleted emails? > >Any advice would be much appreicated. > >Thanks >B > Exmerge if you know the subject. If this is Exchange 2007, then use the built in searching capabilities with PowerShell. ...