Can i extract information from a column

rufuxinix · Feb 13, 2019

I have a sheet with a few tables next to each other.
Besides other tables the two below appear next to each other.

OEOEE HLHLL
EOOEO LHHLH
OOOEO LHHHH
OOEOO LLLHL
OEOEO HLLLH
Etc, any combination of Os and Es and on the second table any combination of Hs and Ls

What I need to do is to extract the first 3 values of the first table and determine how many times, say, OEO has come up on that table
The result should display on a new table such as, based on the above,

OEOEE HLHLL
OEOEO HLLLH

I hope this is clear, is this possible to do in Excel?

Special-K99 · Feb 13, 2019

Re: Can i extact information from a column

Assuming your data is in columns A and B on Sheet 1 and starts on row 1.

in Sheet2!A1
=IFERROR(INDEX($A$1:$B$5,AGGREGATE(15,6,ROW($A$1:$B$5)/((LEFT($A$1:$A$5,3)="OEO")),ROWS(A$1:A1))-(1-1),1),"")
and copy down for as many rows as you have on the Sheet1 column A table.

rufuxinix · Feb 14, 2019

Re: Can i extact information from a column

Thank you very much, Special K, I iwll try it will let you know the result. Much appreciated!

rufuxinix · Feb 14, 2019

Re: Can i extact information from a column

Special-K99 said:
Assuming your data is in columns A and B on Sheet 1 and starts on row 1.

in Sheet2!A1
=IFERROR(INDEX($A$1:$B$5,AGGREGATE(15,6,ROW($A$1:$B$5)/((LEFT($A$1:$A$5,3)="OEO")),ROWS(A$1:A1))-(1-1),1),"")
and copy down for as many rows as you have on the Sheet1 column A table.

_____________________________________________________________________________________________________
Ok, I have tried your suggestion but the result I get back is only one cell where I enter the formula, as below.
I included the complete range, that's the reason for the 953.
By the way I can't figure out what does Aggregate(15,6,
refer to, can you tell me?

Formula i entered
=IFERROR(INDEX($A$1:$B$953,AGGREGATE(15,6,ROW($A$1:$B$953)/((LEFT($A$1:$A$5,3)="OEO")),ROWS(A$1:A1))-(1-1),1),"")

eoooe	lllhh	oeooo
ooeeo	hllhh
oeooo	hlllh
eoooe	hhhhl
eoeeo	lhllh
eeeoe	hhhhl
eoeee	hhhll
oeeoe	hlhhl
eooee	hlhlh
eeeeo	lhhhl

oeooe hlhll

From the above two columns the result I need is

oeooo hlllh
oeooe hlhll

Hope you get what I am saying and can help?

Thanks very much!

Special-K99 · Feb 14, 2019

Re: Can i extact information from a column

Ah, think my formula is wrong. Checking...

AGGREGATE (15, 6... is the SMALL function, it uses an array to calculate results.
Bit complicated to explain.

Special-K99 · Feb 14, 2019

1. Create a blank sheet named Sheet1 and one called Sheet2.
2. Type your example data into columns A and B, so A1:B5
3. Type the following formula into Sheet2!A1

=IFERROR(INDEX(Sheet1!$A$1:$B$5,AGGREGATE(15,6,ROW($A$1:$B$5)/((LEFT(Sheet1!$A$1:$A$5,3)="OEO")),ROWS(A$1:A1))-(1-1),COLUMN()),"")

and copy it down for as many rows as you have in Sheet1 column A (ie 5) and into Sheet2!column B.

Output is two rows on Sheet2

OEOEE HLHLL
OEOEO HLLLH

Extend the ranges in the formula to suit your proper data.
general format of the formula is

'=IFERROR(INDEX(range,AGGREGATE(15,6,ROW(range)/(range to check and condition),ROWS(first cell reference with anchored row - first row))-(first row-1),column),"")

Special-K99 · Feb 14, 2019

In your second table the formula should be

=IFERROR(INDEX(Sheet1!$A$1:$B$11,AGGREGATE(15,6,ROW($A$1:$B$11)/((LEFT(Sheet1!$A$1:$A$11,3)="OEO")),ROWS(A$1:A1))-(1-1),COLUMN()),"")
(range has been extended to row 11 - however many rows you have in Sheet1 column A)

Output is

oeooo hlllh
oeooe hlhll

rufuxinix · Feb 15, 2019

Got it, thank you very much!
I've tested and seems to work well!
Regards.

rufuxinix · Feb 20, 2019

Hi Special K99

A new challenge has showed up, though the formulas are working fine.

If I want to add a new line of numbers to the main list, in Sheet A, I insert a new row in A2.
Doing this affects all the formulas in Sheet B and subsequent Sheets, which I have created. They all now point to A3
and ignore the new data in Row A2.
As I have to keep adding new rows, on a regular basis, how do I get around this problem?

Hope you can help.
Thanks.

Special-K99 · Feb 20, 2019

Not sure if this would work or not, give it a whirl.

Define the range as a Named Range with the row numbers extending beyond what you would need, e.g. if you're likely to have no more than 350 records you could define named range covering up to row 500.Then change the ranges in the formula to the Defined Range name.

=IFERROR(INDEX(NamedRange,AGGREGATE(15,6,ROW(NamedRange)/((LEFT(NamedRange,3)="OEO")),ROWS(A$1:A1))-(1-1),COLUMN()),"")

If you insert a record in within the Named Range, the Named Range adjusts so it would change from row 500 to row 501.

Can i extract information from a column

rufuxinix

New Member

Excel Facts

Special-K99

Well-known Member

rufuxinix

New Member

rufuxinix

New Member

Special-K99

Well-known Member

Special-K99

Well-known Member

Special-K99

Well-known Member

rufuxinix

New Member

rufuxinix

New Member

Special-K99

Well-known Member

Similar threads

Forum statistics

Share this page

Can i extract information from a column

New Member

Excel Facts

Well-known Member

New Member

New Member

Well-known Member

Well-known Member

Well-known Member

New Member

New Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock