INDEX/MATCH or VLOOKUP/HLOOKUP

[chrono2483]

Hello,

I have raw data being pasted. I need a formula to use to extract data from the raw data into another spreadsheet. There is no common heading in which I can reference. Also, the array/range will vary, as the pasted raw data can be shorter or longer. The last thing is that within the raw data, are multiple sections of the same data I need to reference:


[RANGE=cls:xl2bb-100][XR][XH=cs:6]Excel 2012[/XH][/XR][XR][XH][/XH][XH]A[/XH][XH]B[/XH][XH]C[/XH][XH]D[/XH][XH]E[/XH][/XR][XR][XH]1[/XH][XD=h:l|c:ff0000]Brad Smith[/XD][XD=c:ff0000][/XD][XD=h:r|bc:000000][/XD][XD=h:r][/XD][XD=h:r][/XD][/XR][XR][XH]2[/XH][XD=h:c|c:ff0000]Start Time[/XD][XD=h:c|c:ff0000][/XD][XD=h:r|bc:000000][/XD][XD=h:c|fw:b]Scheduled Start Time[/XD][XD=h:c|fw:b]End Time[/XD][/XR][XR][XH]3[/XH][XD=h:c|c:ff0000]From[/XD][XD=h:c|c:ff0000]To[/XD][XD=h:r|bc:000000][/XD][XD=h:c]???[/XD][XD=h:c]???[/XD][/XR][XR][XH]4[/XH][XD=h:c]10:30 AM[/XD][XD=h:c]12:30 PM[/XD][XD=h:r|bc:000000][/XD][XD=h:c]???[/XD][XD=h:c]???[/XD][/XR][XR][XH]5[/XH][XD=h:l|c:ff0000]Peter May-Locke[/XD][XD=c:ff0000][/XD][XD=h:r|bc:000000][/XD][XD=h:c]???[/XD][XD=h:c]???[/XD][/XR][XR][XH]6[/XH][XD=h:c|c:ff0000]Start Time[/XD][XD=h:c|c:ff0000][/XD][XD=h:r|bc:000000][/XD][XD=h:r][/XD][XD=h:r][/XD][/XR][XR][XH]7[/XH][XD=h:c|c:ff0000]From[/XD][XD=h:c|c:ff0000]To[/XD][XD=h:r|bc:000000][/XD][XD=h:c]I need the start from and to times to be
pulled from the data on the left into
the headings above.[/XD][XD=h:c][/XD][/XR][XR][XH]8[/XH][XD=h:c]--:--[/XD][XD=h:c]--:--[/XD][XD=h:r|bc:000000][/XD][XD=h:c][/XD][XD=h:c][/XD][/XR][XR][XH]9[/XH][XD=h:c]8:00 AM[/XD][XD=h:c]9:45 AM[/XD][XD=h:r|bc:000000][/XD][XD=h:r][/XD][XD=h:r][/XD][/XR][XR][XH]10[/XH][XD=h:l|c:ff0000]Regina Petrovik[/XD][XD=c:ff0000][/XD][XD=h:r|bc:000000][/XD][XD=h:r][/XD][XD=h:r][/XD][/XR][XR][XH]11[/XH][XD=h:c|c:ff0000]Start Time[/XD][XD=h:c|c:ff0000][/XD][XD=h:r|bc:000000][/XD][XD=h:r][/XD][XD=h:r][/XD][/XR][XR][XH]12[/XH][XD=h:c|c:ff0000]From[/XD][XD=h:c|c:ff0000]To[/XD][XD=h:r|bc:000000][/XD][XD=h:r][/XD][XD=h:r][/XD][/XR][XR][XH]13[/XH][XD=h:c]--:--[/XD][XD=h:c]--:--[/XD][XD=h:r|bc:000000][/XD][XD=h:r][/XD][XD=h:r][/XD][/XR][XR][XH]14[/XH][XD=h:c]8:15 AM[/XD][XD=h:c]10:00 AM[/XD][XD=h:r|bc:000000][/XD][XD=h:r][/XD][XD=h:r][/XD][/XR][XR][XH]15[/XH][XD=h:r][/XD][XD=h:r][/XD][XD=h:r|bc:000000][/XD][XD=h:r][/XD][XD=h:r][/XD][/XR][XR][XH=cs:6][RANGE][XR][XD]Sheet2[/XD][XH][/XH][/XR][/RANGE][/XH][/XR][/RANGE]

Can an index/match work for this, if the headings are not the same OR would a vlookup/hlookup work? What would the formula look like? I thought the following could work for a index/match but I'm stuck on the fact that I cannot match the name and the fact that I have to index/match in a range that could vary in size, as well the names will change:

=INDEX($A$1:$B$4,MATCH(????)

What would be the best formula to use?

 

[gaz_chops]

Do you not need the name, just the Start & End Times?

 

[chrono2483]

I do need the names. I was able to use a macro to gather the names together and have them pasted into another cell in sequential order so that I can just reference that range of cells. Now I need to match up the start from and to times; yes the names would need to remain.

im curious on what you had in mind in that the macro already copy/pasted the names elsewhere so I guess it could be removed from the original raw data. Are you thinking it is possible by removing the names? (Is it equally possible by keeping the names?)

 

[gaz_chops]

Sorry, so you want to match the times to the names? Or, just a list of times?

 

[chrono2483]

Just a list of times will work

 

[gaz_chops]

Not sure it is possible with a formula (someone else may be able to).
If you just want a list of the Start & End Times in col D/E, then I would record a macro doing the following, then ask for help with the code to get it to work with variable data sizes etc.

RECORD MACRO
Copy col A & B
Create a new sheet
Cursor in cell A1 (on new sheet)
Paste data
Cursor in cell A1 (on new sheet)
Select Sort data - Ascending
Copy all dates
Paste to Original sheet
Delete the new sheet
STOP MACRO

 

[chrono2483]

I tested this out - unfortunately, will not work as there are some names/dates that have multiple start/stop times. By sorting the data, it mixes up the times, thereby I would be unable to match them to the agent separately.

I"m thinking a macro may be a great way to approach this - or a combination of macro/formula.

I have a macro that will extract the names, and paste them in a separate column (ie. Names are found in Col A, and the macro will copy/paste into Col M)

I"m thinking at this point, a combination formula of Index/Match and Offset may work? If I can use a index/match to find the name and then a series of Offset Formulas to navigate to the cell in question may work.

What do you think?

 

[gaz_chops]

I would insert a col & fill with seq no.s, this will keep the sort as original.
This will pull the dates into another column, copy across 1 col
=IF(ISNUMBER(A1),A1,"")
or with the names above
=IF($A2="Start Time",$A1,IF(ISNUMBER(A2),A2,""))

Both will leave spaces when you drag formula down, sort on empty cells (incl new no.s in range), delete empty cells, re-sort using new no. values.

Hope that makes sense! Not sure how much of a help, but maybe if it works, you can code it.

 

[chrono2483]

I didn't quite follow the formula above (tried it out, but returned blank all the time). In lieu, I've tried the Index/Offset but doing the following:

=OFFSET(INDEX($A$1:$A$500,MATCH($I3,$A$1:$A$500,0)),5,,,2)

But for some reason I get an #VALUE! or #REF! error.