Excel help

Hi all, hoping you might be able to help me with something that will streamline some of my employment excel work.

I’ve got two excel spreadsheets.
Spreadsheet A is from last week and outdated.
Spreadsheet B is from this week and the current one with my updated project list.

Spreadsheet A has my notes in column M of excel but doesn’t have my latest projects list.

Can I merge the job numbers in Column A and the notes of Column M in Spreadsheet A (outdated spreadsheet with notes) into spreadsheet B so that the vacant notes rows will be my new projects?
 

randyzany

Premiership Player
Sep 21, 2007
3,951
1,749
Melbourne
AFL Club
Geelong
Seems the OP query has been answered so I hope the OP doesn't mind using this thread instead of making a new one.

I want to create a table or spreadsheet (whichever is easier) from 2 sets of data which are small tables themselves.

1 set of data contains the column reference of a particular value and the other set of data contains the row reference of that value.

The Column Headers are the generic C1, C2, C3, etc.. and the Row Headers R1, R2, R3, etc...



I have these sets of data in the form of 2 mini tables:

VALUE: 1, 2, etc..
COLUMN: 8, 12, etc..


VALUE: 1, 2, etc..
ROW: 5, 6, etc..

From these mini tables I want to make a larger table that places Value 1 in Column 8 and Row 5, Value 2 in C12,R6, etc..

Been googling for a while with no luck in finding out to combine data from 2 tables to form a new one.
 
Last edited:

Caesar

Ex-Huckleberry
Mar 3, 2005
29,398
15,658
Tombstone, AZ
AFL Club
Western Bulldogs
Seems the OP query has been answered so I hope the OP doesn't mind using this thread instead of making a new one.

I want to create a table or spreadsheet (whichever is easier) from 2 sets of data which are small tables themselves.

1 set of data contains the column reference of a particular value and the other set of data contains the row reference of that value.

The Column Headers are the generic C1, C2, C3, etc.. and the Row Headers R1, R2, R3, etc...



I have these sets of data in the form of 2 mini tables:

VALUE: 1, 2, etc..
COLUMN: 8, 12, etc..


VALUE: 1, 2, etc..
ROW: 5, 6, etc..

From these mini tables I want to make a larger table that places Value 1 in Column 8 and Row 5, Value 2 in C12,R6, etc..

Been googling for a while with no luck in finding out to combine data from 2 tables to form a new one.
the search phrases I would use for this problem are 'join tables in Excel' and 'convert table to matrix in Excel'

there are a few different ways to do it, but (assuming you don't have duplicate values) I would use Power Query to join the two tables on the Value column, then use the Pivot Column function to create your columns (under advanced options, select Don't Aggregate)

example file attached
 

Attachments

  • createMatrix.xlsx
    21.5 KB · Views: 61

randyzany

Premiership Player
Sep 21, 2007
3,951
1,749
Melbourne
AFL Club
Geelong
When I first started searching for a solution for this task on Excel I just assumed that it would be fairly straightforward and need only the Excel basics (like stuff you learnt for school assignments) for it to complete it. Have to brush up on those matrices and arrays, thanks for the hints.

Oh and I appreciate the template you've uploaded, thanks again.
 
Mar 3, 2022
4,538
5,920
AFL Club
Adelaide
If I have 2 drop down boxes can I set up a formula so that depending on what value is selected from the drop down boxes (worded selections) it then displays a result in a third box.

For example, the first drop down box are tasks and the second drop down box are departments. Can I then, depending on what someone selects, then display, for example, someone's name?

Also is an excel spreadsheet the best way to achieve this or is there something better?

I got as far as dependent drop down boxes via tutorials but I can't find a way to marry it all up.
 
If I have 2 drop down boxes can I set up a formula so that depending on what value is selected from the drop down boxes (worded selections) it then displays a result in a third box.

For example, the first drop down box are tasks and the second drop down box are departments. Can I then, depending on what someone selects, then display, for example, someone's name?

Also is an excel spreadsheet the best way to achieve this or is there something better?

I got as far as dependent drop down boxes via tutorials but I can't find a way to marry it all up.

As far as if Excel is the best tool for it, you haven't given enough detail on what you're trying to achieve.

What you're describing there is pretty simple though. Easiest way in my mind would be to put Data Validation on the first two cells and set it to a list of allowed values that you set elsewhere on the sheet (or another sheet). In the third cell, you apply a formula to concatenate the values from the first two cells, and then do a VLookup to another sheet where you're storing Key/Value pairs.

That way you don't have to muck around with VBA or any ActiveX stuff.
 
Back