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?

rufuxinix · Feb 21, 2019

Special-K99 said:
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.

Thanks very much for your reply and suggestions, Special
I replied to this yesterday but don't see it anywhere, any longer, some gremlin didn't let it go through

When I created the sheets I made provision for additional rows being inserted, so i defined the range up to 1000. At the moment I have about 960 rows in Sheet1

So i have Sheet1, with data starting at Row 2. In Sheet2 i have the following in A2
=IFERROR(INDEX(Sheet1!$A$2:$Z$1003,AGGREGATE(15,6,ROW($A$1:$Z$1001)/((LEFT(Sheet1!$A$2:$A$1003,3)="OOO")),ROWS(A$1:A1))-(1-1),COLUMN()),"")

If I insert a row and add data in Row 2 Sheet1, this comes up in Sheet2
=IFERROR(INDEX(Sheet1!$A$3:$Z$1004,AGGREGATE(15,6,ROW($A$1:$Z$1001)/((LEFT(Sheet1!$A$3:$A$1004,3)="OOO")),ROWS(A$1:A1))-(1-1),COLUMN()),"")

I thought maybe the only way around this is to have the additional blank rows above the 1st entry in Sheet1 and define them from the start. I have tried it quickly by cutting and pasting the existing data in Sheet1 but it doesn't work, Sheet2 follows the data rows and it becomes
=IFERROR(INDEX(Sheet1!$A$23:$Z$1024,AGGREGATE(15,6,ROW($A$1:$Z$1001)/((LEFT(Sheet1!$A$3:$A$1004,3)="OOO")),ROWS(A$1:A1))-(1-1),COLUMN()),"")

Special-K99 · Feb 21, 2019

1. Since your data starts on row 2 it should be

=IFERROR(INDEX(Sheet1!$A$2:$Z$1000,AGGREGATE(15,6,ROW($A$2:$Z$1000)/((LEFT(Sheet1!$A$2:$A$1000,3)="OOO")),ROWS(A$1:A1))-(2-1),COLUMN()),"")

2. There are no named ranges in the above formula.
You need to define a Named Range as A$2:Z$1000 then when you insert rows within that range the Named range should adjust.

rufuxinix · Feb 22, 2019

Thanks for the reply.
I forgot to mention that the named range did not make a difference
I defined the Named Range but if i insert a row into Row 2, the Range moves down 1 Row, so from being A2:Z1000 it becomes is A3:Z1000.

I tried the alternative of naming a range a2:z1000 and start inputting the data from row a5.
I then input the new data at row a4 but it does not update the other sheets.
I tried closing and opening the Excel to see if refreshing it would help but didn't.

If any suggestions I will appreciate it.

Thanks for the ongoing help!
Regards.

rufuxinix · Mar 18, 2019

Hi Special K99
I guess I was in a rush and didn't do it properly.
I was going on holiday, from which I have just returned.
I have now tried your suggestion again and it works, after all. Ie, I named the range and I can introduce new rows at the top , and it readjusts itself.
Thanks for the help!

By the way, this program has become too slow because it has so many dependencies I have created (sheets).
Is there a way of transferring the whole Excel program to make it process faster?
Perhaps in Visual Basic or C++ language?

Special-K99 · Mar 18, 2019

I dont know C++ and am not a VBA expert Im afraid

Can i extract information from a column

rufuxinix

Board Regular

rufuxinix

Board Regular

Excel Facts

Special-K99

Well-known Member

rufuxinix

Board Regular

rufuxinix

Board Regular

Special-K99

Well-known Member

Similar threads

Forum statistics

Share this page

Can i extract information from a column

rufuxinix

Board Regular

rufuxinix

Board Regular

Excel Facts

Special-K99

Well-known Member

rufuxinix

Board Regular

rufuxinix

Board Regular

Special-K99

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