Data Organized

Dear All,

I have list of names (column A) and every time I put a code (example: 
2), instantly the function will create a list in another column.

For example
Data:
A	B
NAME	CODE
AA
BB	2
CC
DD	2
EE

Result:
BB
DD

And if I put another code (3 for example), the function will create 
another list at next column.

Please help.

Best Regards,
Gusur
0
gusur_oke (4)
9/27/2009 1:00:16 PM
excel 39879 articles. 2 followers. Follow

6 Replies
402 Views

Similar Articles

[PageSpeed] 27

Try this array formula

=IF(ISERROR(SMALL(IF($B$1:$B$20=COLUMN(B1),ROW($A$1:$A$20)),ROW($A1))),"",
INDEX($A$1:$A$20,SMALL(IF($B$1:$B$20=COLUMN(B1),ROW($A$1:$A$20)),ROW($A1))))

copy down and across as required

-- 
__________________________________
HTH

Bob

"Gusur" <gusur_oke@hotmail.com> wrote in message 
news:%238yP1J3PKHA.4428@TK2MSFTNGP02.phx.gbl...
> Dear All,
>
> I have list of names (column A) and every time I put a code (example: 2), 
> instantly the function will create a list in another column.
>
> For example
> Data:
> A B
> NAME CODE
> AA
> BB 2
> CC
> DD 2
> EE
>
> Result:
> BB
> DD
>
> And if I put another code (3 for example), the function will create 
> another list at next column.
>
> Please help.
>
> Best Regards,
> Gusur 


0
BobNGs (423)
9/27/2009 4:29:32 PM
Dear Bob,

Maybe I'm not clearly to explain.

I have 200 employees name with department code in next column (total 5 
departments). And I want make 5 groups/lists of employees name suitable 
with their dept code.

I need this cause some of employee move to difference dept and I don't 
want to make 5 new lists manually. :(

Many Thanks
Gusur


Bob Phillips wrote:
> Try this array formula
> 
> =IF(ISERROR(SMALL(IF($B$1:$B$20=COLUMN(B1),ROW($A$1:$A$20)),ROW($A1))),"",
> INDEX($A$1:$A$20,SMALL(IF($B$1:$B$20=COLUMN(B1),ROW($A$1:$A$20)),ROW($A1))))
> 
> copy down and across as required
> 
0
gusur_oke (4)
9/28/2009 12:09:36 AM
I think that is exactly what I gave you. It is not manual, it is dynamic 
based upon the original data.

Have you tried it?

-- 
__________________________________
HTH

Bob

"Gusur" <gusur_oke@hotmail.com> wrote in message 
news:O2ep3$8PKHA.5108@TK2MSFTNGP02.phx.gbl...
> Dear Bob,
>
> Maybe I'm not clearly to explain.
>
> I have 200 employees name with department code in next column (total 5 
> departments). And I want make 5 groups/lists of employees name suitable 
> with their dept code.
>
> I need this cause some of employee move to difference dept and I don't 
> want to make 5 new lists manually. :(
>
> Many Thanks
> Gusur
>
>
> Bob Phillips wrote:
>> Try this array formula
>>
>> =IF(ISERROR(SMALL(IF($B$1:$B$20=COLUMN(B1),ROW($A$1:$A$20)),ROW($A1))),"",
>> INDEX($A$1:$A$20,SMALL(IF($B$1:$B$20=COLUMN(B1),ROW($A$1:$A$20)),ROW($A1))))
>>
>> copy down and across as required
>> 


0
BobNGs (423)
9/28/2009 8:40:27 AM
Dear Bob,

I have tried it.

I have list of employees name at column A with header "NAME" and "DEPT. 
CODE" in next column with value between 1 to 5. I put your formula in 
cell D2, copy it down and the result is not I expected. I expected list 
of employee name with dept. code = 1 at column D, dept. code = 2 at 
column E and so on.

Did I miss something?

Best Regards,
Gusur

Bob Phillips wrote:
> I think that is exactly what I gave you. It is not manual, it is dynamic 
> based upon the original data.
> 
> Have you tried it?
> 
0
gusur_oke (4)
9/28/2009 2:50:13 PM
You originally said you wanted code 2, so I (blindly) coded it to start at 
code 2. If you want to start at code 1, change COLUMN(B1) to COLUMN(A1) in 
the formula.

What did you get when you tried it?

-- 
__________________________________
HTH

Bob

"Gusur" <gusur_oke@hotmail.com> wrote in message 
news:u%23rx6rEQKHA.1372@TK2MSFTNGP02.phx.gbl...
> Dear Bob,
>
> I have tried it.
>
> I have list of employees name at column A with header "NAME" and "DEPT. 
> CODE" in next column with value between 1 to 5. I put your formula in cell 
> D2, copy it down and the result is not I expected. I expected list of 
> employee name with dept. code = 1 at column D, dept. code = 2 at column E 
> and so on.
>
> Did I miss something?
>
> Best Regards,
> Gusur
>
> Bob Phillips wrote:
>> I think that is exactly what I gave you. It is not manual, it is dynamic 
>> based upon the original data.
>>
>> Have you tried it?
>> 


0
BobNGs (423)
9/28/2009 3:06:19 PM
Dear Bob,

Still not working.
But I got reference file from Pete_UK.
With minor modification, it's work perfectly.

Thanks to you both.

Best Regards,
Gusur

Bob Phillips wrote:
> You originally said you wanted code 2, so I (blindly) coded it to start at 
> code 2. If you want to start at code 1, change COLUMN(B1) to COLUMN(A1) in 
> the formula.
> 
> What did you get when you tried it?
> 
0
gusur_oke (4)
9/28/2009 11:26:47 PM
Reply:

Similar Artilces:

The data area passed to a system call is too small
Hello, I have just tried to use some working VC++6.0 code compiled with the ..NET2003 complier and now get the error "The data area passed to a system call is too small" - error code 122. Can anyone help? Thanks, Clive "Clive Taylor" <CliveTaylor@discussions.microsoft.com> wrote in message news:D6C135E1-1D56-4385-A133-705B83041E38@microsoft.com... > Hello, > > I have just tried to use some working VC++6.0 code compiled with the > .NET2003 complier and now get the error "The data area passed to a system > call is too small" - error cod...

Auto time recording in another cell for data entered in one
I need to record the time any data is entered in a cell of a particula coloumn. Example: In coloumn A if data is entered in cell A10 the automatically current time is recorded in cell N10. If there is any macro for this then pls let me know -- Message posted from http://www.ExcelForum.com Hi you may also have a look at http://www.mcgimpsey.com/excel/timestamp.html -- Regards Frank Kabel Frankfurt, Germany > I need to record the time any data is entered in a cell of a > particular coloumn. Example: In coloumn A if data is entered in cell > A10 then automatically current time i...

Comparing data and updating spreadsheets
I have two spreadsheets with Last Name and First Name columns. What I would like to do is compare the Last Name and First Name fields in Spreadsheet A to the same fields in Spreadsheet B. If there's a match, I would like to take the UserID from Spreadsheet A and update the UserID column in Spreadsheet B. What is the easiest way to accomplish this? when you say update the User ID on sheet two, is there an existing number which will be changed. if so, I would add a new column with the old IDs on sheet two and add a new IDcolumn wit =if(iserror(sumproduct(--(Sh1!Lastrange=last),--(Sh...

formatting data series
Hello, I have Excel 2007. I have a stacked bar chart comprising 4 data 'series' . I want to change the formats of the bars. I know how to select a series & format it, but can I format all 4 series simultaneously , or can I format one and copy that format to the others (I only want the bar colours to be different) Thanks KK ...

Microsoft Excell does not format the data numbers right justified.
Hi. I am using Microsoft Excell and can not format the data in the cells as I want. For example, I want to format the cells with the dollar sign and right justified but when I click on the buttons under format cells, it does not do what I want. I have no idea why this does not work but it seems like it should. I have tried it at least 20 times and it never works or gives me any indication why. It may be that there is some mode or something turned on that I do not know about. Please help. Thanks. Do you realize that *before* you choose exactly what formats you wish to use, you *must* se...

internet data link using wildcard
I want to link data from one excel spreadsheet to another over the internet, however the source file changes everyday because the date is part of the files name. Can I use wildcard? ...

[2007] Any way to transparently import data from txt ?
Hi there Excel developers, Is there any way to transparently import data into a sheet using the same parameters than when you do it manually but without asking for anything and without displaying the import wizard, just pass its parameters? The operation consists of: - Data -> Import from TXT - Then tell it "Fixed Width" and "Start at row 40" - Then set the column separators (break lines) by positions - Then import the data Is this possible programmatically? Without recording a macro? Thanks for any help. Sincerely, Steve JORDI (Remove the K_I...

Data Matching 01-22-10
Hello, I have a question. I have two sets of data - one large and one small. The large one has metadata, and the small one fits entirely inside the large one. I need to separate out the metadata from the large set for just the entries in the small set. For example, say I have these three columns: abc 123 123 bcd 234 567 cde 456 678 def 567 901 efg 678 012 fgh 789 ghi 890 hij 901 ijk 012 jkl 321 klm 432 Where column A is the metadata, column B is the large set, and column C is the small set. I want to create another column (o...

outlook business contact manager and existing outlook data
I have a new computer where I just installed office pro 2003 and outlook business contact manager. How should I import my outlook data from my old computer which has outlook with out business contact manager. Can I just copy over the pst file to the new computer or is it a different process? Thanks. -- moondaddy@noemail.noemail Yes, just copy the pst over and use it as the default message store. You should only use import if you had an older version of outlook and want to use the new pst format. On 4/28/06 4:24 PM, in article u6FSxGwaGHA.3408@TK2MSFTNGP04.phx.gbl, "moondaddy...

Create Diffgram Based on 2 Different XML Files or Data Sets
I have an xml file that my application downloads on a periodic basis. I also have a dataset based on this that is used in the application. At present, the application takes the new downloaded xml, creates a data set from that and merges it with one in use. The ultimate goal is to have the update of the primary data set trigger change events based on only data that has changed (updates, additions, or deletions). I am thinking that I need to generate a diffgram based on the current data and the newly downloaded data and then apply that to the current dataset. Is this the propper approach...

Monitor Amount Of Data Downloaded
I was wondering if there was any way of finding out how much data (emails) in terms of MB exchange processes/downloads on a monthly basis. On Tue, 6 Feb 2007 21:00:00 -0800, Stephen <Stephen@discussions.microsoft.com> wrote: >I was wondering if there was any way of finding out how much data (emails) in >terms of MB exchange processes/downloads on a monthly basis. Lots, there are 3rd party applications that take your message tracking logs and present them in graph and report formats Quest and Promodag are good packages for the large and small/med orgs respectively. Are there a...

Graphing Large Amounts of Data
TWIMC I have a datalog which contains a large amount of info (+10000 data points). When I try and plot anything more than 1000 points in Excel 2007, the app starts to labour quite seriously and takes forever to fomat a simple thing like axis scale. I assume that this is because the new Excel has some pretty fancy formatting options and it also runs live previewing as formats are changed within dialogue boxes. Is there a way to just draw simple scatter plots without any of the processor consuming embelishments? I just need basic formatting functionality. Thanks As far as I've b...

Removing Legacy Data from a PivotChart report
I have a pivot chart report which pulls its source data from SQL Server database. I have created a copy of this file to point to a different SQL Server (exact same tables). When I refresh the report, the chart and pivot are refreshed correctly from the new server. However the original data still remains (as well as the new data) in the drop down selection box for the columns. i.e. the chart is showing ExecuteDate in the ROW area, JobName in the COLUMN area and the number of minutes each job ran each day as the DATA. The graph plots each job showing the duration over the last 30 days. In the...

How to find the data source
Hi, and Thank you for any help. I have inherited a hand full of Pivot table spreadsheets that Pull data in from an Access DB, and the query used in the spread sheet needs to be modified (it is using some Dates that should not be hard coded). There are a few that I just can not find any Links, or Data Queries, nothing that looks like it is calling out to the data source, except it does prompt me to refresh the data when I open the file. I've found all the hidden sheets, and named ranges (I think) I've looked through all the VBA also. How do I find what I can't see?? Thanks....

Chart with data from multiple worksheets
Is there a way to create a chart that uses data (range) from multiple worksheets in both x and y axis. In my case I have several worksheets for different cities in one file. Within each worksheet are three columns. Column 1 identifies a point of interest in the city, column 2 measures the distance from the airport to said point of interest and column 3 has the elevation of the point of interest. I wish to chart the distances on the x axis and the elevations on the y axis using the data from the each city worksheet directly without creating another worksheet that references all the da...

Data validation #20
Hi...I am trying to store a list in a seperate worksheet and the reference it on my other worksheets. When I go to data validation an try to do this, i get the message "you may not reference anothe worksheet or workbook for data validation." My cpu at home has exce 2003, this one has 2002. Any ideas or workarounds? Thank -- Message posted from http://www.ExcelForum.com Hi Try the following: - select your list - create a defined name for this list ('Insert - Name Define') - use this name in your data validation formula. e.g. =Name_list have a look at the following htt...

Pie Chart from data
Hi, I am trying to create a pie chart from the below data just to show the percentages of what work orders are maj, med and min. How do I set this up!! Thanks in Advance!!! how can i create a pie char By following the directions within the Chart Wizard. -- Regards, Tushar Mehta, MS MVP -- Excel www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article <9265C290-B0B6-4CAD-B909-5EDFCE7DC415@microsoft.com>, anonymous@discussions.microsoft.com says... > how can i create a pie chart > > ...

Growing/Shrinking/Selective Chart Data
I'm looking for a Dynamic means of populating a bar chart. Cloumn A (Rows 2-200) Has the Names (Y Axis) of each Item and Cloumn B (Rows 2-200) (X Axis) has the Number Values. My problem is twofold... One, every week the Y axis list can grow or shrink and Two, I only want to show those that have an X Axis value that is greater than five. Anything less than or equal to five I would like to have hidden from showing in the chart. Is it possible to have some sort of way or formula that can make this possible in a chart or in pivot table? Thanks in Advance. 1. First, put the X values to...

Socket Problem
Hello everybody, I have a strange problem with socket communikation. I'm using MFC CAsyncSocket to devellop a client-server application. The client is a third party program which I cannot change. The client disconnects after sending a telegramm and reconnects for sending the next. Server listens to a port, client connects, server accepts and receives data. So far - so good. However, after a fiew telegramms succesfully send and received, the server gets no more data. The connect-accept works fine but there is no OnReceive after sending data. I've written a dummy client to reproduc...

counting blocks of data
this is a sample of a large block of data I have. A block of data is represented by the letter A until the next cell is Blank. For E.G. row 1 has 2 blocks of data. the 1st block is row1, col3 and 4 and the next block is column 9. row 2 has 2 blocks. Col3,col4 and col5 is the 1st block and column 8 & 9 is the 2nd block. Row 3 has 1 block only. column 6&7 Row 4 has 0 blocks and row 5 has 1 block because all the A's are in adjacent cells. 1 2 3 4 5 6 7 8 9 Row 1 A A A Row 2 A A A A A Row 3 A A Row 4 Row 5 A A A A Thank you all for you...

Importing Fixed Width data
Hi, I've been extracting information from my company mainframe, and placing fields into a "fixed length record" file, which I then FTP, and import into Excel 2000. Each record contains around 200 fields, and the import process is VERY painful, for two reasons. - many of the fields are single byte, and clicking to set the start/end point of each is a nightmare. - I generally need to put many of my fields into 'Text' format, as much of the data has leading zeros, which need to be retained. Is there a way that I can set up an import 'template', so that I can just...

Reading in an xls data file
Should I not be able to read an XSL file into the XMLDocument to modify it etc? When I do I get Zero items into my collections. Here is the code FileStream myFile = new FileStream("C:\\mystyles\\styles\\sdoc.xsl", System.IO.FileMode.Open, System.IO.FileAccess.Read) StreamReader myReader = new StreamReader(myFile) XmlDataDocument myXmlDataDocument = new XmlDataDocument() tr myXmlDataDocument.Load(myReader) You should be using XmlDocument.Load... -- Daniel Cazzulino [MVP XML] Clarius Consulting SA http://weblogs.asp.net/cazzu http://aspnet2.com "BobTheHacker" <...

How to select an axis that is covered by data points?
In some chart I would like to adjust some parameters of the X-axis (esp. the minimum and maximum, since the values choosen automatically are a bit "uneven" and it would be nice to have a range of 0..20'000 and tickmarks at 5'000, 10'000 etc, than an axis of 1..17495 and tickmarks at other odd numbers. But my charts has >20000 data points and many of those lie on or very near the X-axis. So, how can I select the X-axis itself (in order to be able to do a right-click and then select "Format Axis")? Whenever I try to select the axis I only select the da...

LINEST
I would like to use the LINEST function on data that is not located in one single row or column. The data that have to be correlated are scattered along the worksheet and I would like to select them manually. How can I do this. If I try =LINEST((E72;F72;G72);(E75;F75;G75),1,1) or =LINEST({E72;F72;G72}; {E75;F75;G75},1,1) an error occurs. How can I input an array of nonadjacent cells in this function. thx Why not use some 'helper cells' with formulas like =E72 to get the numbers into a contiguous range? best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme ...

Is Excel or Word better for holding and searching data
In Microsoft Office 2003 to create a small d ata base for use by staff accustomed to Word and Excel, which is the more useful for (1) easy retrieval of information, and (2) possibly putting onto a web page? I'd definitely use Excel for this. HTH. Best wishes Harald "Ione" <Ione@discussions.microsoft.com> skrev i melding news:A7500E03-AF10-4D9F-9E56-887DF6E3DE51@microsoft.com... > In Microsoft Office 2003 to create a small d ata base for use by staff > accustomed to Word and Excel, which is the more useful for > (1) easy retrieval of information, and ...