Some Noob Questions

[Windyisle]

Hi there. I'm very new to excel and a lot of the answers here are over my head. I'm totally okay with learning what to do, but I just need someone to narrow down what I'm supposed to be looking for.

I need to find a number in one excel file:
terms will look like this: HD #1423 -blah blah blah - I need to grab '1423'

Then look up the listing for the item in another file - I want data in two of the columns

Paste this data into a third file

I also want to lookup how many times a certain phrase is mentioned and return the number to a cell in my third file

I know this stuff is probably pretty easy. I'm happy to learn what I need to know, but if someone could tell me what functions I should be learning about to make this happen, it'll make my life a lot easier.

Thanks,

WindyIsle

 

[cornflakegirl]

Welcome to the board.

As a starter for ten, I would suggest MID to grab 1423, VLOOKUP for looking up in columns and COUNTIF for counting instances of a phrase.

But we would need to see a sample of your data to offer more useful advice.

 

[Windyisle]

Awesome.

Yes. Ok. Now I'm starting to make some headway. Really all I needed was a few terms that I could start learning about without feeling overwhelmed.

Here's a more detailed description of what I'm doing, if anyone knows where to point me in the right direction, that would rock the casaba.

I work for a broadcaster and I'm doing music reports. Basically, any time a segment airs on the network I need to send information on what music it had in it, the composer and the duration the music played. There is a master list of all the links that have this information.

What I need to do is somehow cross-reference what aired that day and either enter a value of times a certain link played (for links that played a lot, like station IDs) - which I'm figuring out with MID and COUNTIF. But also certain pieces have special music info, so I need new rows to be added to the bottom of the report which are filled with info from the sheet.

To clarify further:

Program Breakdown
HD #1423 -this thing la la la
5 15 - episode of something I don't need to look up
HD #0606 - Station ID
HD #0501 - other thing herpy derpy doo

Music Master list
LINK 1423 | Segment title | CD and CUT | This Publisher | This duration
LINK 1424 | Segment title | CD and CUT | This Publisher | This duration
etc

Music Cue Sheet (what I'm making)

station ID 1 |this info already filled in | x 12 <- MID and Countif figuring out
station ID 2 |this info already filled in | x 14
Then I need to add rows to the bottom of the list with extra segments:

Segment title | CD and CUT | This publisher | This Duration (link 1424)

Some may take up more than one line because some segments have multiple music cues. So does anyone know how to add a line (or more) with the information I tell it to grab?

Help is appreciated.

 

[cornflakegirl]

I don't entirely follow. If you were to compile the music cue sheet manually, how would you do it? Step by step - where would each piece of information come from, how would you know it was the right information etc.

 

[Windyisle]

basically, here's how I'm starting to understand what I need to do: (none of this is in 'excel speak' so that's where I'm hoping for help).

Look for a term (eg: "station id")
when found, either count the number of times it shows up OR retrieve certain information and put that information in new rows.

I could program a different command for every term that I look for, theres only 20 or so - I just need to figure out how to search for a string and return full rows that get added to the bottom of the table.

Does that help.

P.S. And yes, up till now, everyone counts manually on printouts, then looks up the numbers and types in the info...

 

[cornflakegirl]

Okay, I'm still not quite following!

Here's what I think you're after - let me know how it compares with reality...

In your music cue sheet you have:

Cell A3: Station ID 1 Cell B3: Some info Cell C3: Occurence count
A4: Station ID 2 B4: Some info C4: Count
A5:A24 Lookup terms 1-20

And in B5:B24 you're looking to populate with eg titles
In C5:C24 populate with albums
D5:D24 populate with publishers

Does this vaguely correlate?

If so, I think that you need to use VLOOKUP to retrieve the info from the Master list that matches your lookup terms. (If you need help with VLOOKUP syntax, shout).

If not, could you please elucidate further?