How can I get match data from a comma separated list to pull cell value from same row?

brhudgens

New Member
Joined
Feb 3, 2011
Messages
2
Hello,

I have 2 tabs in my workbook. The first tab (Vlist) looks something like this:

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D+[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]CODE[/TD]
[TD]FORMAT[/TD]
[TD]Item#[/TD]
[TD]many more columns-->[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]C01[/TD]
[TD]X[/TD]
[TD]1598, 1599, 1600, 1601, 1604, 1608, 1609, 1611, 1612, 1613, 1614, 1616, 1629, 1630, 1631, 1632, 1633, 1634, 1635, 1636, 1637, 1638, 1639, 1640, 1641, 1643, 1644, 1649, 1650, 1651, 1652, 1654, 1656, 1657, 1658, 1913, 1914, 1915, 1916, 1917, 1918, 1920, 1921, 1922, 1923, 1924, 1925, 1928, 1929, 1932, 1935, 1939, 1940, 1950, 4973, 4974, 6690[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]C02[/TD]
[TD]Y[/TD]
[TD]1930, 1936[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]C03[/TD]
[TD]Z[/TD]
[TD]1645[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]C04[/TD]
[TD]W[/TD]
[TD]1603[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]C05[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]up to 64[/TD]
[TD]C64[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]




























The second tab (Recap) looks like this:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Item#[/TD]
[TD]ItemName[/TD]
[TD]Code[/TD]
[TD]more columns of data-->[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1598[/TD]
[TD]Name1599[/TD]
[TD]C01[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1599[/TD]
[TD]Name1600[/TD]
[TD]C01[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1600[/TD]
[TD]Name1601[/TD]
[TD]C01[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]1603[/TD]
[TD]Name1645[/TD]
[TD]C03[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]1930[/TD]
[TD]Name1930[/TD]
[TD]C02[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]up to 200[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


The restrictions that have been placed on the solution are as follows:
- no duplicate data entry required from the user
- no macros
- no additional interaction required from the user entering the data to make it work (no sorting or other manipulation).
- The VList tab is in order by CODE, The Recap tab is in order by Number

My goal is to do the following:
- CODE is already populated in the VList tab and has to be updated infrequently.
- Item# is already populated in the Recap tab and has to be updated infrequently.
- The relationship between the Code and the Item# changes weekly, so I need to do one of the following to avoid having to have the user do duplicate data entry:
A) Have the User enter the Item#s into the VList tab in a comma separated list to show all the Item#s that apply to a Code. Then in the Recap tab be able to use the Item# to find the corresponding code and populate the Code column in the Recap tab.

OR

B) Have the User enter the Codes in the Recap tab for each Item#, then use the Code from the VList tab to get a comma separated list of all of the Item#s that have that code.

I don't have a good solution for either option at this point, given the restrictions.

I've always had a lot of luck finding great solutions on this forum and I hope that you can help me with this puzzle. Please let me know if there's any additional info I can provide to clear up my request.

Thanks very much!
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
MATCH takes wild cards.

In Recap!C2, you could put the formula =INDEX(Vlist!A:A, MATCH("*"A2&"*", Vlist!C:C, 0), 1)
 
Upvote 0
Sorry for my delay in replying - pulled off to another project, but I'm back on this now....

Thanks for your response. This works great!

Do you know of a way that I could generate the comma separated listing in Vlist (column C) if the user fills in the Code in Recap column C? The comma separated list is harder for the users to manage manually and they would like to go the other direction, entering the Code on Recap and the comma separated list on Vlist being generated for them. Thoughts?
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

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