Webimport daily updated table with variable number of objects

[Cervantes]

I want to create a daily updated table of data imported from an internet site. I used the import from web feature in excel to create a raw ‘current data table’. I then linked these data to another sheet using the specific layout I want to use for this project, where I also want to keep historic prices for all objects. However the number of objects on the website is variable each day and I don’t know how to properly link them and the prices to my other sheet.

Ideally I would like something like:
find “object X” in the raw spreadsheet A1:A500 and find the value corresponding to object X “value X” in the raw spreadsheet B1:B500.

Is this possible? Or is there a faster way?

Help very much appreciated.

[Fotis1991]

Looks like INDEX&match issue. Did you try this?

Or else,pls, upload a small sample workbook

[Cervantes]

I hadn't tried yet, but after your suggestion I looked into it briefly but couldn't find out how to make it work.

I attached a small sample of my excell worksheet now. The first sheet is the import sheet, column A is how I get my data delivered from the web. Since I downloaded American numbers, I had to convert the ". " to "," which I did in column B.
In the second sheet I have made a table in the layout I want. In this sheet I used simple links cause I didn't know the number of objects would change from day to day (there's a 0.50 value threshold for things to get listed). So in my current system if there's an additional item next day, the whole table flips and becomes useless.

Hope this sample helps.

[Fotis1991]

Really i am not able to follow you.

In the second sheet, do you want the names of Column A in first sheet and then in Column B the values?

Also what are thesw numbers in column D?

[Cervantes]

Yes exactly, in the second sheet I want column A to give the names and column B to give the current values (updated daily through the website)
All other columns will give historic prices for each day, which I will copy manually from column B. For now that was just nov 27th, yesterday's prices. Column C shows the difference between today and the pprevious day.

However, because the number of objects listed varies from day to day the names in the first sheet will differ from day to day (not much, probably just a few changes each day). But column A in the second sheet should always list all names (even the ones not currently on the imput sheet) matched by current prices from the imput sheet (and a blank cell in case the name is not on the imput sheet).

I've added another sample which basically only shows the results I want (so no formulas, only kept the one in column B of the imput sheet cause I figured I'm gonna need that one anyway).

Sheet1: Imput28-11 is the imput I get from the website on nov. 28
Sheet2: Overview28-11 columns A & B is what I want to get as a result (the other columns I manage myself)
Sheet3: Imput29-11 is an example of the imput file from tomorrow (in the final version obviously this would be in the same sheet as the previous imput file, simply by refreshing the data)
Sheet4: Overview29-11 columns A & B is what I want as a result. Notice I added two new objects in the imput file (AAA and AAB) and these are now listed among the names in column A. Also I cut one name "Angelic Overseer" from the imput file which is why that object is listed as a blank in the current prices column B.

Hope this makes things clearer. Basically I want to store historical prices for objects by daily clicking refresh in the imput sheet and than copying column B in the overview sheet to a new column labeled with the current date.


edit: after trying some more myself, I found that LOOKUP gives me almost what I need. I can copy all object names from the first imput file into column A of my overview sheet (onetime action). Then in column B use LOOKUP(Ax;'IMPUT28-11'!A1:B100) to get the updated value for that given object each day. Remaining problem is, how to spot new objects?
Is there a way to compare the list of names from the imput sheet with the list of names from my overview sheet and have excell show me the differences?

[Fotis1991]

Apologize for the delay, but today is a really hard day for me.

Take a look to the example. I hope to be ok for you.

[Cervantes]

Many thanks for the help, I really couldn't have figured this out myself. This file seems to do almost exactly what I asked for, however a) I can't seem to replicate it in the actual file and more importantly b) if objects are added or removed my whole history shifts with it. So I'm sad to say this isn't a workable solution for me.

I've decided to take a different approach and manually fill in all the object names in the overview sheet. All excel needs to do is find the corresponding value from the IMPUT sheet for any given object. This I still can't figure out.
I've attached the full file, with in the IMPUT sheet the actual web imported data and in the OVERVIEW sheet a list of all the objects. My question is how to fill column B of the OVERVIEW sheet with the values corresponding to the objects?

[Fotis1991]

In Input sheet B20, put youe formula and copy down. Tis will be a helper and hide column.

=VALUE(SUBSTITUTE(A20,".",","))

then in Overniew B3 put this and copy down.

=INDEX(IMPUT!$B$19:$B$292,MATCH(OVERVIEW!A3,IMPUT!$A$19:$A$292,0))

[Cervantes]

Great, looks like it works!!

Just for good measure, I started the formula in column B in the imput sheet in B19 with =VALUE(SUBSTITUTE(A20,".",",")) cause I want the value (A20) of the object in A19 to be listed in B19.
I also found away for excel to find objects who are not in my overview list, so looks like I'm done here.

Once again many thanks for the help and I added to your rep!

[Fotis1991]

You are welcome.

Glad that i was able to help you.

Thanks for the reb*