Compressing a List Into Consecutive Rows

Twinky

New Member
Joined
Sep 10, 2009
Messages
16
Office Version
  1. 365
Does anyone have an idea how to make this happen?:

Sheet 2 has an extended list of codes in column C. Column D is where values are entered when needed. Some rows might get values entered and some may not.

On sheet 1, I need to compile a list showing the codes and their corresponding values. However, I need the list on sheet 1 to be in successive rows. That is, the list on sheet 1 would display the first value with a code, from sheet 2, show up in row 1 and the next value with a code would show up in row 2, the next in row 3 etc. etc.

So if sheet 2 has a value in D1, and it’s corresponding code in C1, and the next code & value pair is down is in C9/D9, how would the row 1 on sheet 1 detect and display the C1/D1 code & value pair on sheet 2 and then have the C9/D9 pair show in row 2?

I've attached two images that mock-up what I'm describing.

Big thanks to anyone who can chime in!
 

Attachments

  • Sheet 1.PNG
    Sheet 1.PNG
    10.7 KB · Views: 6
  • Sheet 2.PNG
    Sheet 2.PNG
    12.8 KB · Views: 6

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
 
Upvote 0
Hello Twinky

You do not need a second sheet. Just use the filter.
 
Upvote 0
How about
Excel Formula:
=FILTER(Sheet2!C1:D1000,Sheet2!D1:D1000<>"")
 
Upvote 0
Glad we could help & thanks for the feedback.
 
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