Large???... Extract???...

How_Do_I

How_Do_I

Well-known Member
Joined
Oct 23, 2009
Messages
1,843
Office Version
  1. 2010
Platform
  1. Windows
Hi all…
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p> </o:p>
I can’t think today…
<o:p> </o:p>
In the Jeanie below I pulled the data from Column X to Column Z in the order I want…
<o:p> </o:p>
In AA I need to look down Column X, find the first 4 and pull the data next to the first 4… then the next 4 etc…
<o:p> </o:p>
How do I do that please?

Excel Workbook
WXYZAA
99, 1, 2146, 4, 2
108, 3, 1145, 3, 4
118, 2, 223
127, 3, 233
137, 2, 323
147, 1, 433
156, 4, 243
166, 3, 333
176, 2, 432
185, 6, 112
195, 4, 322
205, 3, 441
215, 2, 511
225, 1, 611
234, 6, 211
244, 5, 311
254, 4, 411
264, 3, 531
274, 1, 711
283, 6, 311
293, 4, 511
302, 6, 431
312, 4, 611
32
33
34
35
36
37
38
Sheet1
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Sort by date Sort by votes
did you try to just copy and paste the values from w and x to Z and AA, then highlight Z and AA, then do data sort by z, then sort by AA?
 
Upvote 0
bryonwoods30 said:
did you try to just copy and paste the values from w and x to Z and AA, then highlight Z and AA, then do data sort by z, then sort by AA?
Click to expand...

No... I would like formulas to solve this one, you can see the formula in my Jeanie for Column Z...
 
Upvote 0
How_Do_I said:
Hi all…
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
I can’t think today…
<o:p></o:p>
In the Jeanie below I pulled the data from Column X to Column Z in the order I want…
<o:p></o:p>
In AA I need to look down Column X, find the first 4 and pull the data next to the first 4… then the next 4 etc…
<o:p></o:p>
How do I do that please?

Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 52px"><COL style="WIDTH: 52px"><COL style="WIDTH: 52px"><COL style="WIDTH: 52px"><COL style="WIDTH: 52px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>W</TD><TD>X</TD><TD>Y</TD><TD>Z</TD><TD>AA</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD style="TEXT-ALIGN: center">9, 1, 2</TD><TD style="TEXT-ALIGN: center">1</TD><TD> </TD><TD style="TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center">6, 4, 2</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: center">8, 3, 1</TD><TD style="TEXT-ALIGN: center">1</TD><TD> </TD><TD style="TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center">5, 3, 4</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD style="TEXT-ALIGN: center">8, 2, 2</TD><TD style="TEXT-ALIGN: center">2</TD><TD> </TD><TD style="TEXT-ALIGN: center">3</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD style="TEXT-ALIGN: center">7, 3, 2</TD><TD style="TEXT-ALIGN: center">3</TD><TD> </TD><TD style="TEXT-ALIGN: center">3</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD style="TEXT-ALIGN: center">7, 2, 3</TD><TD style="TEXT-ALIGN: center">2</TD><TD> </TD><TD style="TEXT-ALIGN: center">3</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">14</TD><TD style="TEXT-ALIGN: center">7, 1, 4</TD><TD style="TEXT-ALIGN: center">3</TD><TD> </TD><TD style="TEXT-ALIGN: center">3</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">15</TD><TD style="TEXT-ALIGN: center">6, 4, 2</TD><TD style="TEXT-ALIGN: center">4</TD><TD> </TD><TD style="TEXT-ALIGN: center">3</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">16</TD><TD style="TEXT-ALIGN: center">6, 3, 3</TD><TD style="TEXT-ALIGN: center">3</TD><TD> </TD><TD style="TEXT-ALIGN: center">3</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">17</TD><TD style="TEXT-ALIGN: center">6, 2, 4</TD><TD style="TEXT-ALIGN: center">3</TD><TD> </TD><TD style="TEXT-ALIGN: center">2</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">18</TD><TD style="TEXT-ALIGN: center">5, 6, 1</TD><TD style="TEXT-ALIGN: center">1</TD><TD> </TD><TD style="TEXT-ALIGN: center">2</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">19</TD><TD style="TEXT-ALIGN: center">5, 4, 3</TD><TD style="TEXT-ALIGN: center">2</TD><TD> </TD><TD style="TEXT-ALIGN: center">2</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">20</TD><TD style="TEXT-ALIGN: center">5, 3, 4</TD><TD style="TEXT-ALIGN: center">4</TD><TD> </TD><TD style="TEXT-ALIGN: center">1</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">21</TD><TD style="TEXT-ALIGN: center">5, 2, 5</TD><TD style="TEXT-ALIGN: center">1</TD><TD> </TD><TD style="TEXT-ALIGN: center">1</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">22</TD><TD style="TEXT-ALIGN: center">5, 1, 6</TD><TD style="TEXT-ALIGN: center">1</TD><TD> </TD><TD style="TEXT-ALIGN: center">1</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">23</TD><TD style="TEXT-ALIGN: center">4, 6, 2</TD><TD style="TEXT-ALIGN: center">1</TD><TD> </TD><TD style="TEXT-ALIGN: center">1</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">24</TD><TD style="TEXT-ALIGN: center">4, 5, 3</TD><TD style="TEXT-ALIGN: center">1</TD><TD> </TD><TD style="TEXT-ALIGN: center">1</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">25</TD><TD style="TEXT-ALIGN: center">4, 4, 4</TD><TD style="TEXT-ALIGN: center">1</TD><TD> </TD><TD style="TEXT-ALIGN: center">1</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">26</TD><TD style="TEXT-ALIGN: center">4, 3, 5</TD><TD style="TEXT-ALIGN: center">3</TD><TD> </TD><TD style="TEXT-ALIGN: center">1</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">27</TD><TD style="TEXT-ALIGN: center">4, 1, 7</TD><TD style="TEXT-ALIGN: center">1</TD><TD> </TD><TD style="TEXT-ALIGN: center">1</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">28</TD><TD style="TEXT-ALIGN: center">3, 6, 3</TD><TD style="TEXT-ALIGN: center">1</TD><TD> </TD><TD style="TEXT-ALIGN: center">1</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">29</TD><TD style="TEXT-ALIGN: center">3, 4, 5</TD><TD style="TEXT-ALIGN: center">1</TD><TD> </TD><TD style="TEXT-ALIGN: center">1</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">30</TD><TD style="TEXT-ALIGN: center">2, 6, 4</TD><TD style="TEXT-ALIGN: center">3</TD><TD> </TD><TD style="TEXT-ALIGN: center">1</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">31</TD><TD style="TEXT-ALIGN: center">2, 4, 6</TD><TD style="TEXT-ALIGN: center">1</TD><TD> </TD><TD style="TEXT-ALIGN: center">1</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">32</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">33</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">34</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">35</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">36</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">37</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">38</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>Z9</TD><TD>=IF(ROWS(Z$9:Z9)>$B$7,"",LARGE($X$9:$X$38,ROWS(Z$9:Z9)))</TD></TR><TR><TD>Z10</TD><TD>=IF(ROWS(Z$9:Z10)>$B$7,"",LARGE($X$9:$X$38,ROWS(Z$9:Z10)))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4
Click to expand...
Try this...

In the formula I use the following defined named ranges:
  • Strings
  • Refers to: =$W:$W
  • Values
  • Refers to: =$X$9:$X$31
Then, this array formula** entered in AA9 then copied down as needed:

=INDEX(Strings,SMALL(IF(Values=Z9,ROW(Values)),COUNTIF(Z$9:Z9,Z9)))

** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.
 
Upvote 0
Hi Biff...

I can't get that working...

Have I done something wrong please?

Excel Workbook
WXYZAA
99, 1, 2142
108, 3, 114#NUM!
118, 2, 223#NUM!
Sheet1
#VALUE!
</td></tr></table></td></tr></table>
 
Upvote 0
I'll be away for a while. I'll look more closely when I get back.
 
Last edited:
Upvote 0
Hi Biff...

Hope you see this before you have a look at my problem...

This got the result I wanted...

=INDEX(Strings,SMALL(IF(Values=Z9,ROW(Values)-ROW($X$9)+1),COUNTIF(Z$9:Z9,Z9)))

Thanks mate, you got my brain thinking on the right lines! :)
 
Upvote 0
How_Do_I said:
Hi Biff...

Hope you see this before you have a look at my problem...

This got the result I wanted...

=INDEX(Strings,SMALL(IF(Values=Z9,ROW(Values)-ROW($X$9)+1),COUNTIF(Z$9:Z9,Z9)))

Thanks mate, you got my brain thinking on the right lines! :)
Click to expand...
Ok, good deal!

However...

If you defined the range names as I showed then you don't need to calculate an offset correction.
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Need help! extract value in special case, find many method, cannot solve it!
Replies
6
Views
176
CLKinfo
C
I
  • Solved
Irregular Data Set
2
Replies
19
Views
342
IdabaMalouki
I
G
  • Solved
If Else Loop macro running slow, anyway to speed it up?
Replies
9
Views
299
Alex Blakenburg
A
R
  • Solved
Macro To Move Files - Will Not Move .msg or .csv files
Replies
4
Views
104
rachel06
R
D
  • Solved
Comparing characters in two strings and looking for a difference
Replies
2
Views
84
dgparryuk
D

Forum statistics

Threads
1,224,537
Messages
6,179,405
Members
452,911
Latest member
a_barila

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top