Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
RicardoE
 
Posts: n/a
Default Excel: How to choose data on two separate rows in the same column

Hello,

I have data that has the same NAME repeating on two separate rows of the
same column; the NUMBER column is different. Here is an example of what this
data,
located in Tab C of my spreadsheet, might look like:

COL A: NAME COL B: NUMBER
D0_DIMM_CLK0_A_H 4.1506626
D0_DIMM_CLK0_A_H 4.714883
D0_DIMM_CLK0_A_L 4.1341208
D0_DIMM_CLK0_A_L 4.7194365

As you can see, the data in COL A repeats, but the data in COL B is different.
In a separate Tab, call it Tab A, of my spreadsheet, I have set up a formula
that needs to use the 1st instance of this data; to do this search, I use the
following
formula:

=VLOOKUP(A1,TabC!A:B,2,FALSE)

where A1 has the value "D0_DIMM_CLK0_A_L" in it. So this works perfectly
fine.
The value returned is 4.1506626.

In Tab B of the spreadsheet, I have another set of data that need to use the
*second* value of the instance of "D0_DIMM_CLK0_A_L"; in other words, I need
to use the 4.714883 value. However, if I try using the same VLOOKUP equation
I used above, I'll still get the 4.1506626 value.

What can I do to specify I want to skip the first instance of the data, and
find and use the data from the second instance of this data? I'm not sure
how the INDEX or MATCH functions could help here.

Certainly, I could split up the two instances of the data to occur on two
separate columns, but the problem with that approach is that I have a lot of
data that is constantly changing and it would be extremely difficult to go
split it all up each time the data is updated.

Any help with this task will be greatly appreciated.

Thanks!

rei.

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Biff
 
Posts: n/a
Default Excel: How to choose data on two separate rows in the same column

Hi!

Is there always a second instance and might there be a third, fourth, fifth?

Can't use entire columns as references:

=LOOKUP(2,1/(TabC!A1:A5=A1),TabC!B2:B5)

This will find the LAST instance, be it the second, third, fourth, etc.

Biff

"RicardoE" wrote in message
...
Hello,

I have data that has the same NAME repeating on two separate rows of the
same column; the NUMBER column is different. Here is an example of what
this
data,
located in Tab C of my spreadsheet, might look like:

COL A: NAME COL B: NUMBER
D0_DIMM_CLK0_A_H 4.1506626
D0_DIMM_CLK0_A_H 4.714883
D0_DIMM_CLK0_A_L 4.1341208
D0_DIMM_CLK0_A_L 4.7194365

As you can see, the data in COL A repeats, but the data in COL B is
different.
In a separate Tab, call it Tab A, of my spreadsheet, I have set up a
formula
that needs to use the 1st instance of this data; to do this search, I use
the
following
formula:

=VLOOKUP(A1,TabC!A:B,2,FALSE)

where A1 has the value "D0_DIMM_CLK0_A_L" in it. So this works perfectly
fine.
The value returned is 4.1506626.

In Tab B of the spreadsheet, I have another set of data that need to use
the
*second* value of the instance of "D0_DIMM_CLK0_A_L"; in other words, I
need
to use the 4.714883 value. However, if I try using the same VLOOKUP
equation
I used above, I'll still get the 4.1506626 value.

What can I do to specify I want to skip the first instance of the data,
and
find and use the data from the second instance of this data? I'm not sure
how the INDEX or MATCH functions could help here.

Certainly, I could split up the two instances of the data to occur on two
separate columns, but the problem with that approach is that I have a lot
of
data that is constantly changing and it would be extremely difficult to go
split it all up each time the data is updated.

Any help with this task will be greatly appreciated.

Thanks!

rei.



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
RicardoE
 
Posts: n/a
Default Excel: How to choose data on two separate rows in the same col

Hi,

This works very well, for what I need to do. Thank you very much!

rei.


"Biff" wrote:

Hi!

Is there always a second instance and might there be a third, fourth, fifth?

Can't use entire columns as references:

=LOOKUP(2,1/(TabC!A1:A5=A1),TabC!B2:B5)

This will find the LAST instance, be it the second, third, fourth, etc.

Biff

"RicardoE" wrote in message
...
Hello,

I have data that has the same NAME repeating on two separate rows of the
same column; the NUMBER column is different. Here is an example of what
this
data,
located in Tab C of my spreadsheet, might look like:

COL A: NAME COL B: NUMBER
D0_DIMM_CLK0_A_H 4.1506626
D0_DIMM_CLK0_A_H 4.714883
D0_DIMM_CLK0_A_L 4.1341208
D0_DIMM_CLK0_A_L 4.7194365

As you can see, the data in COL A repeats, but the data in COL B is
different.
In a separate Tab, call it Tab A, of my spreadsheet, I have set up a
formula
that needs to use the 1st instance of this data; to do this search, I use
the
following
formula:

=VLOOKUP(A1,TabC!A:B,2,FALSE)

where A1 has the value "D0_DIMM_CLK0_A_L" in it. So this works perfectly
fine.
The value returned is 4.1506626.

In Tab B of the spreadsheet, I have another set of data that need to use
the
*second* value of the instance of "D0_DIMM_CLK0_A_L"; in other words, I
need
to use the 4.714883 value. However, if I try using the same VLOOKUP
equation
I used above, I'll still get the 4.1506626 value.

What can I do to specify I want to skip the first instance of the data,
and
find and use the data from the second instance of this data? I'm not sure
how the INDEX or MATCH functions could help here.

Certainly, I could split up the two instances of the data to occur on two
separate columns, but the problem with that approach is that I have a lot
of
data that is constantly changing and it would be extremely difficult to go
split it all up each time the data is updated.

Any help with this task will be greatly appreciated.

Thanks!

rei.




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Need to Improve Code Copying/Pasting Between Workbooks David Excel Discussion (Misc queries) 1 January 6th 06 04:56 AM
Excel Macro to Copy & Paste [email protected] Excel Worksheet Functions 0 December 1st 05 02:56 PM
Loading Column Data with blank Rows into Data Validation Box ExcelMonkey Excel Worksheet Functions 3 October 13th 05 06:09 PM
Want to auto hide rows in excel when no data in a certain column. Tim Excel Discussion (Misc queries) 3 June 30th 05 12:52 AM
How to sort/update large excel db [email protected] Excel Discussion (Misc queries) 0 February 2nd 05 01:43 AM


All times are GMT +1. The time now is 11:38 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"