Help to extract data

[dissertationhelp]

Hi,
I am struggling with excel and I cant get any staff help until Monday but I need work done this weekend. Basically, I have a huge data set with stock returns and I need to extract certain columns. The columns already have the required information so I simply just need something to search for the relevant index and then return whatever is in that column.
For example, I have an announcement with the index 1234, somewhere in the large data (row 1) ththere is the index 1234 and I need the data in the column. So if say cell ABC1 was the cell with index 1234 I need all the data in column ABC.
The large data set has approx. 28k columns, so 15k then under 13k columns, and I have split up the separate announcement types in order to take a sub sample so this will need done multiple times.
I hope this makes sense to someone.
I have also cross posted this on:
http://www.mrexcel.com/forum/excel-q...ml#post4081379


Just as I need some help asap.
Thanks guys

Book2.xlsx

[sourabhg98]

Hi
It would be good if you could upload a sample workbook here and mock up your requirements manually in the sheet..
You can upload by clicking on the Go Advanced button below the typing area and then paper clip icon--
http://www.excelforum.com/members/da...ch-a-file.html

[dissertationhelp]

I attached a very simple example, its not the real data but hopefully illustrates the idea

[sourabhg98]

Hello
Try this in L3 and copy down
=INDEX(B3:E3,,$L$1)

Hope this helps!!

[dissertationhelp]

That helps thanks, but I have 9000 announcements so I will need 9000 different columns extracted from the large data set, so I don't see how that formula would do that? Or would it?

[apo]

HI.. Sorry.. but i can't really fully understand what you want.. what's the relevance of the FTSE values?

But.. from what i do understand.. if youjam all your values into an array and use Application.Index to get whole columns values.. then I am thinking that is what you might need..

[dissertationhelp]

So I am carrying out an event study, I have a company in the top column (index1234) and directly under it the FTSE values, so I need both to be extracted. I have 28k unique index's that are relevant to each separate announcement. I have generated subsamples, so a list of index's I need extracted from the original 28k announcements and I need these columns to be extracted. I need to do this for initiations, increases, decreases and omissions (so 4 subsamples) and increases has 9000 unique index that need to be extracted? does this make any more sense?
Thanks for any help

[sourabhg98]

I am extremely sorry but I am still not getting what do you need exactly...sorry again

[dissertationhelp]

just searching for a number of specific index numbers in a large data set and extract the column that the index is heading

[sourabhg98]

ok
So you have several index numbers in row A1 like in the example 1,2,3 and 4 so first you want to extract unique numbers from them like if you have 1,1,1,2,2,3,4,1,2,5 you want to first extract unique numbers i.e. 1,2,3,4,5 and then extract the values below them?
Is this what you are trying to get?

[dissertationhelp]

so index numbers start at 1 and go to 27873 as they are unique identifiers for each announcement. I have a list of 9000 random index numbers so could be anywhere in the range 1-27873 and I want to extract all the columns that are headed by the index numbers. So for example I have index numbers 55, 197, 674, 900 then I want to extract the columns with the headers 55, 197, 674 and 900.

[sourabhg98]

Check the attached
If this is what you need...

see the page 2 of This Thread If U Have not

[apo]

Hi..

I am pretty sure that your requirements are quite achievable.. but I think you need to attach a more relevant sample..
Things like:

28k columns, so 15k then under 13k column

Confuse the reader..

Are you mixing up columns with rows..?

Like i say.. An array based solution using Application.Index is how i would probably go..

[dissertationhelp]

im not sure, but I think what I need is similar to a vlookup but for lots of cells and a column instead of a row

[dissertationhelp]

souabhg98, that almost worked, some of the cells appeared #REF! and it also didn't copy the column directly below (i.e the ftse column)
apo, the file is too large to attach, I cant think of how else to explain it sorry

[sourabhg98]

ohh i think that I am going wrong
I think you need this
=HLOOKUP(B1,Data!$B$1:$XFD$23,ROW(A3),FALSE)

[sourabhg98]

as their are 16384 columns in an excel sheet so if you enter a number greater than 16384 then #REF! error will return

[sourabhg98]

As vlookup Is For Column Similarly Hlookup is Meant For rows...so use the Above in B2 and Copy Across
Just Change B1 to B$1 to Fix in the above formula

[dissertationhelp]

if I used hlookup I would manually need to change the lookup term which for 9000 companies would be pointless I think
I think what I need should be simple but explaining it correct is hard.
would a fancy if formula work? so say IF excel finds this index in this row of index then 1 else 0 then just sort high to low so all the number 1's are at the start?

[sourabhg98]

No need to change manually ....just Change B1 in my hlookup formula To B$1

[dissertationhelp]

but when i copy across to the next 8999 formula then it will stay say B1 when the next is c1

[sourabhg98]

No when you do B$1 it just fixes 1 not B ....B would be fixed if you use $B$1...so put only B$1