help with vlookup or index or match or what?

R

rcirone

Active Member
Joined
Mar 12, 2009
Messages
483
Office Version
  1. 365
Platform
  1. Windows
I have 4 taps
1. Two-Ten tab
2. Loc tab
3. Purple Tap
4. Data Dump Tab
I am try get the info from data dump tab to all the other taps with the right Program. if you look at the pic you will see what I need to do please help I do not know if I use vlookup index or what?
Sorry but I can not change the order of the data dunp Tab.
Please Help?
work1.jpg
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Sort by date Sort by votes
I think i get whats going on, I can do this via VBA if you can put the data dump in its own tab. Are these all setup as tables?
 
Last edited:
Upvote 0
Maybe this:

Array formula - use Ctrl+Shift+Enter and not only Enter to enter the formula

Code: 
=IFERROR(INDEX('Data Dump'!$A$2:$L$8,
SMALL(IF('Data Dump'!$D$2:$D$8=MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))+1,99),ROW('Data Dump'!$D$2:$D$8)-ROW('Data Dump'!$D$2)+1),ROWS(A$2:A2)),
COLUMNS($A2:A2)),"")

PS: use the same formula in each sheet (Purple,LOC and Two-Ten)

Markmzz
 
Upvote 0
It's not clear at all to me what you want to happen, to what and to where or why.

What info from data dump do you want to go to other tabs and how does one know what tab the information is to go to that tab.

...other taps with the right Program. What does this mean?

Maybe pick a name in data dump and explain clearly what you want to happen to that name and associated info on that row.

Regards,
Howard
 
Upvote 0
a11jks said:
I think i get whats going on, I can do this via VBA if you can put the data dump in its own tab. Are these all setup as tables?
Click to expand...

Yes all yellow tab are there own tab I just put it this way so you can all see what I need.
 
Upvote 0
I am looking to get the info from the data dump tab to the other yellow tabs. each of the yellow tabs are there own tabs. So I am looking to get the info from the data dump taps (under two-ten program to goto it own tab. so all I have to do is to copy and paste the data in and all the tabs with auto work like two-ten tab, loc tab, purple tab etc.....
 
Upvote 0
The easiest way to do this IMO is using the following logic with VBA

For each row in data dump table, minus the header row
Determine what the current row's program is
Then for each cell in that row, write it to the correct sheet.

In order to do this, I will need to know what your entire data dump looks like. How many columns, what are they called, what kind of data do they hold, what formula do you use in each calculated cell already, etc.
 
Upvote 0
markmzz said:
Maybe this:

Array formula - use Ctrl+Shift+Enter and not only Enter to enter the formula

Code: 
=IFERROR(INDEX('Data Dump'!$A$2:$L$8,
SMALL(IF('Data Dump'!$D$2:$D$8=MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))+1,99),ROW('Data Dump'!$D$2:$D$8)-ROW('Data Dump'!$D$2)+1),ROWS(A$2:A2)),
COLUMNS($A2:A2)),"")

PS: use the same formula in each sheet (Purple,LOC and Two-Ten)

Markmzz
Click to expand...

Hi Rcirone,

Did you try my formula of the post #3 (the above formula)?

If yes, what you got?

Here is the result with my sample workbook

Agent Name
E#
Supervisor Name
Program
Phone
True
Total
Productive
Lunch
Non-
Talk
Calls
Data Dump
Name01
2608
Name10
Purple
08:33:39
176
23:11:20
Data01
Data07
Data13
Data19
Data25
Name02
2235
Name11
Two-Ten
23:34:11
278
13:58:13
Data02
Data08
Data14
Data20
Data26
Name03
8618
Name12
Two-Ten
21:43:06
275
19:51:27
Data03
Data09
Data15
Data21
Data27
Name04
2146
Name13
Two-Ten
23:02:37
162
06:51:07
Data04
Data10
Data16
Data22
Data28
Name05
8976
Name14
LOC
12:03:53
282
02:16:39
Data05
Data11
Data17
Data23
Data29
Name06
8239
Name15
LOC
17:14:44
202
03:37:44
Data06
Data12
Data18
Data24
Data30
Name07
8425
Name16
Two-Ten
01:37:11
299
05:15:56
Data07
Data13
Data19
Data25
Data31
*
Agent Name
E#
Supervisor Name
Program
Phone
True
Total
Productive
Lunch
Non-
Talk
Calls
Two-Ten
Name02
2235
Name11
Two-Ten
23:34:11
278
13:58:13
Data02
Data08
Data14
Data20
Data26
Name03
8618
Name12
Two-Ten
21:43:06
275
19:51:27
Data03
Data09
Data15
Data21
Data27
Name04
2146
Name13
Two-Ten
23:02:37
162
06:51:07
Data04
Data10
Data16
Data22
Data28
Name07
8425
Name16
Two-Ten
01:37:11
299
05:15:56
Data07
Data13
Data19
Data25
Data31
*
Agent Name
E#
Supervisor Name
Program
Phone
True
Total
Productive
Lunch
Non-
Talk
Calls
LOC
Name05
8976
Name14
LOC
12:03:53
282
02:16:39
Data05
Data11
Data17
Data23
Data29
Name06
8239
Name15
LOC
17:14:44
202
03:37:44
Data06
Data12
Data18
Data24
Data30
*
Agent Name
E#
Supervisor Name
Program
Phone
True
Total
Productive
Lunch
Non-
Talk
Calls
Purple
Name01
2608
Name10
Purple
08:33:39
176
23:11:20
Data01
Data07
Data13
Data19
Data25
************
*****
*****************
********
*********
*****
*********
***********
********
*******
*******
*******
***********

<tbody>
</tbody>


Markmzz
 
Upvote 0
if this is an one-off exercise, you may consider to use Advance Filter. Quick and easy and no Formula is required.


rcirone said:
I have 4 taps
1. Two-Ten tab
2. Loc tab
3. Purple Tap
4. Data Dump Tab
I am try get the info from data dump tab to all the other taps with the right Program. if you look at the pic you will see what I need to do please help I do not know if I use vlookup index or what?
Sorry but I can not change the order of the data dunp Tab.
Please Help?
work1.jpg
Click to expand...
 
Upvote 0
rcirone said:
I have 4 taps
1. Two-Ten tab
2. Loc tab
3. Purple Tap
4. Data Dump Tab
I am try get the info from data dump tab to all the other taps with the right Program. if you look at the pic you will see what I need to do please help I do not know if I use vlookup index or what?
Sorry but I can not change the order of the data dunp Tab.
Please Help?
...
Click to expand...

Activate Two-Ten tab.

Row 1 is supposed to have the headers in the same order as Data Dump tab.

Add an extra column in front of the current first column. The headers start now at B1.

In A1 enter: Idx, which is an additional headers.

A2, control+shift+enter, not just enter, and copy down:
Rich (BB code): 
=IFERROR(SMALL(IF('Data Dump tab'!$D$2:$D$100="Two-Ten",
  ROW('Data Dump tab'!$D$2:$D$100)-ROW('Data Dump tab'!$D$2)+1),
  ROWS($A$2:A2)),"")

B2, just enter, copy across, and down:
Rich (BB code): 
=IF($A2="","",INDEX('Data Dump tab'!A$2:A$100,$A2))

The foregoing is the fastest set up we can have. Adjust to suit and repeat it for the other tabs.
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question
Create new invoice number
Replies
4
Views
137
RonBuss
R
C
  • Question
Index Match question
Replies
2
Views
77
Cubist
Cubist
C
  • Question
Index/Match | Index/Small Formula
Replies
5
Views
340
Eric W
Eric W
N
  • Question
Index Match after sorting
Replies
1
Views
273
Eric W
Eric W
T
  • Question
Help with formula
Replies
3
Views
234
awoohaw
awoohaw

Forum statistics

Threads
1,214,935
Messages
6,122,337
Members
449,078
Latest member
skydd

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top