auralfixstation
New Member
- Joined
- Sep 12, 2014
- Messages
- 1
Basically, I'm dealing with two different databases of BWAV audio files. One of them (I) reflects my current database, where I have embedded metadata descriptions(I-C), and filenames that are strings of numbers(I-A). The other database (II) has matching embedded descriptions (II-A), but with better filenames (II-C). Both databases also have durations, but they don't match perfectly, and are somehow formatted differently (a whole other topic, it seems).
In trying to pull the filenames from II-C into database I, VLOOKUP gets me some of the way there - the problem is that I'm basically doing (in database I) =VLOOKUP(C1,!'II.csv'$A$1:$C$14,3,FALSE), but there are some duplicate fields in column C, so they only ever return the first instance they come across.
I feel like I need to do something like an INDEXMATCH that will look at both durations and the descriptions - but then there's the issue of the durations not matching perfectly, and being differently formatted - so if there was a way for me to say "match duration within x tolerance". Or, I could try something where it says that if the returned value is already in the column, then look for the next instance of the lookup value.
I realize that I could also just generate my own filenames in the first database, but I'm interested in trying to use some sort of INDEX or other lookup function to do it because I have had other similar scenarios at times.
Oh, and apologies for not using a better formatted tool to show my examples...couldn't get MrExcel HTML to work on Mac Office 2011.
Thank you in advance for any tips!
I - Current database
<tbody>
</tbody>II.
<tbody>
</tbody>
In trying to pull the filenames from II-C into database I, VLOOKUP gets me some of the way there - the problem is that I'm basically doing (in database I) =VLOOKUP(C1,!'II.csv'$A$1:$C$14,3,FALSE), but there are some duplicate fields in column C, so they only ever return the first instance they come across.
I feel like I need to do something like an INDEXMATCH that will look at both durations and the descriptions - but then there's the issue of the durations not matching perfectly, and being differently formatted - so if there was a way for me to say "match duration within x tolerance". Or, I could try something where it says that if the returned value is already in the column, then look for the next instance of the lookup value.
I realize that I could also just generate my own filenames in the first database, but I'm interested in trying to use some sort of INDEX or other lookup function to do it because I have had other similar scenarios at times.
Oh, and apologies for not using a better formatted tool to show my examples...couldn't get MrExcel HTML to work on Mac Office 2011.
Thank you in advance for any tips!
I - Current database
A | B | C | D |
S_02183_001.WAV | 00:09.5 | Vocals - Mouth Pops - Int - CU - Various Pitched Hollow Pops | |
S_02183_002.wav | 00:06.2 | Vocals - Mouth Pops - Int - CU - Various Pitched Hollow Pops | |
S_02183_003.wav | 00:13.0 | Vocals - Mouth Pops - Int - CU - Various Pitched Hollow Pops | |
S_029221.wav | 00:07.0 | Pops - Rubber Pops - CU - Quick Pops of Various Pitches, Some Movement | |
S_029223.wav | 00:04.0 | Pops - Bottle Pops - CU - Short Hollow Pops of Various Pitches | |
S_029596.wav | 00:21.8 | Boats Ships - 41' Sailboat Motor - Ext - ECU Start W Splash & Light Rev,Idle, & Engine Off | |
S_029597.wav | 02:05.7 | Boats Ships - 41' Sailboat Motor - Ext - CU Start & Idle W Splashes | |
S_029598.wav | 03:02.9 | Boats Ships - 41' Sailboat - Ext - CU Idle, Short Stop, Into Reverse & Back-up W/ Splashes | |
S_029599_001.wav | 02:30.3 | Boats Ships - 41' Sailboat - Ext - CU Water Trickling Against The Bow | |
S_029599_002.wav | 02:09.2 | Boats Ships - 41' Sailboat - Ext - CU Water Trickling Against The Bow | |
S_029600.wav | 02:23.1 | Backgrounds - Harbor Ambiance - Foghorn Blasts - Periodic Blasts W Light Water Laps | |
S_029601.wav | 01:53.9 | Boats Ships - 41' Sailboat - Ext - MCU Bow Wash W Bg Foghorn | |
<tbody>
</tbody>
A | B | C |
Vocals - Mouth Pops - Int - CU - Various Pitched Hollow Pops | 0:06 | Vocals-MouthPops-Int-CU-V_1.wav |
Vocals - Mouth Pops - Int - CU - Various Pitched Hollow Pops | 0:13 | Vocals-MouthPops-Int-CU-V_2.wav |
Vocals - Mouth Pops - Int - CU - Various Pitched Hollow Pops | 0:09 | Vocals-MouthPops-Int-CU-V_3.wav |
Pops - Rubber Pops - CU - Quick Pops of Various Pitches, Some Movement | 0:06 | Pops-RubberPops-CU-QuickPop.wav |
Boats Ships - 41' Sailboat Motor - Ext - ECU Start W Splash & Light Rev,Idle, & Engine Off | 0:21 | BoatsShips-41'SailboatMotor.wav |
Boats Ships - 41' Sailboat Motor - Ext - CU Start & Idle W Splashes | 2:05 | BoatsShips-41'SailboatMot_1.wav |
Boats Ships - 41' Sailboat - Ext - CU Idle, Short Stop, Into Reverse & Back-up W/ Splashes | 3:02 | BoatsShips-41'Sailboat-Ext.wav |
Boats Ships - 41' Sailboat - Ext - CU Water Trickling Against The Bow | 2:30 | BoatsShips-41'Sailboat-Ex_1.wav |
Boats Ships - 41' Sailboat - Ext - CU Water Trickling Against The Bow | 2:09 | BoatsShips-41'Sailboat-Ex_2.wav |
Backgrounds - Harbor Ambiance - Foghorn Blasts - Periodic Blasts W Light Water Laps | 2:23 | Backgrounds-HarborAmbiance.wav |
Boats Ships - 41' Sailboat - Ext - MCU Bow Wash W Bg Foghorn | 1:53 | BoatsShips-41'Sailboat-Ex_3.wav |
<tbody>
</tbody>