Index Match Multiple Values vertically

vikrampnz

Board Regular
Joined
Jun 20, 2006
Messages
111
Office Version
  1. 2007
Platform
  1. Windows
Hello

I am using excel 2007. I have a worksheet with Floor levels in column A and Activities in header row C1:M1 and I have inserted a Month in each cell co-relating to column and row for that floor/activity.

Test - Index Match Monthly Activity.xlsx
ABCDEFGHIJ
1Aug-23WING ARCCBrickworkInternal PlasterWATERPROOFINGELECTRICALFLOORING / TILING / COUNTERSWOODEN WORKS - DOORSALUMINIUM WORKS
2A-BASEMENT2SLAB 1Done
3
4A-BASEMENT 1SLAB 2Done
5
6A-LOWER GROUNDSLAB 3DoneDoneSep-23Oct-23
712
8A-UPPER GROUNDSLAB 4DoneDoneSep-23Aug-23Sep-23Oct-23Nov-23Nov-23
9101233
10A-OFFICE STILTSLAB 5DoneDoneOct-23Aug-23Aug-23Nov-23Dec-23Jan-24
11200345
12A-OFFICE FIRSTSLAB 6DoneDoneOct-23Sep-23Aug-23Dec-23Jan-24Jan-24
13210455
14A-2SLAB 7DoneSep-23Nov-23Nov-23Sep-23Aug-24Feb-24Dec-24
15133112616
16A-3SLAB 8DoneSep-23Nov-23Dec-23Sep-23Aug-24Nov-24Dec-24
171341121516
18A-4SLAB 9Sep-23Oct-23Dec-23Dec-23Oct-23Sep-24Nov-24Dec-24
1912442131516
Sheet4



I am trying get this data in a Monthwise Format (see below) where multiple activities that scheduled in that Month and their respective floor are shown under that Month, so that it becomes easy for me to monitor the activities that are exepcted to be completed in that month.

Test - Index Match Monthly Activity.xlsx
BCDEFGHI
2Sep-23Oct-23Nov-23Dec-23
3FloorActivityFloorActivityFloorActivityFloorActivity
4A-UPPER GROUNDInternal PlasterA-5RCC
5A-2BrickworkA-6RCC
6A-3Brickwork
7A-UPPER GROUNDInternal Plaster
8A-UPPER GROUNDELECTRICAL
Result


I have entered above values manually to display the expected outcome. The list can be sorted by the Floor level or Activity.

I tried multiple options found on google, but am struggling to get my head around it.

Would really appreciate any help !
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Hi @vikrampnz

I could help you with a macro to get the result.

Do you want me to help you with the macro?

--------------
I hope to hear from you soon.
Respectfully
Dante Amor
--------------
 
Upvote 0

Forum statistics

Threads
1,223,893
Messages
6,175,239
Members
452,621
Latest member
Laura_PinksBTHFT

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