Forum Discussion
Solved
How to Inputing datas to a specific rows and column in a big table
hello, a new member here... so I tried to make this matrix, these these are the data I have :
| Distribution | Destination | Source |
| 108971.00 | 1 | 1 |
| 319103.00 | 2 | 1 |
| 118248.00 | 3 | 2 |
| ........ | ......n | ,,,,,,,,n |
And I have to make a big matrix of distribution from the source to destination, something like this...
| Destination | |||||
| 1 | 2 | 3 | ...n | ||
| 1 | 108971.00 | 319103.00 | 0 | ..... | |
| Source | 2 | 0 | 0 | 118248.00 | ..... |
| 3 | ..... | ..... | ..... | ..... | |
| ...n | ..... | ..... | .......... | ..... |
but I got confused how, anyone have any idea how? I have tried to make a new table and use vlookup/ifs function but got confused in the middle. I wished anyone can help, I will appreciate them very much ❤️
ymnalya Two options for you in the attached file. Since you mention that it concerns a "big table", I would prefer to tackle this with Power Query (PQ). The result is in the green table and requires only one step after connecting to the data (the blue table), and that is to pivot by destination and source. Change the data and Refresh the query for updated results.
If you are not familiar with PQ, the link below contains good material to help you get started.
https://exceloffthegrid.com/power-query-introduction/
You can't use a standard pivot table for this as the Distribution codes are probably texts. It would require Power Pivot and a DAX measure to display texts in the value area of a pivot table.
The other option uses INDEX and MATCH functions. Note that I have used a structured table (blue) for the data, resulting in structured table references which are preferable over direct cell references. Table references will expand and contract automatically with changing sizes of the data range.
ymnalya Two options for you in the attached file. Since you mention that it concerns a "big table", I would prefer to tackle this with Power Query (PQ). The result is in the green table and requires only one step after connecting to the data (the blue table), and that is to pivot by destination and source. Change the data and Refresh the query for updated results.
If you are not familiar with PQ, the link below contains good material to help you get started.
https://exceloffthegrid.com/power-query-introduction/
You can't use a standard pivot table for this as the Distribution codes are probably texts. It would require Power Pivot and a DAX measure to display texts in the value area of a pivot table.
The other option uses INDEX and MATCH functions. Note that I have used a structured table (blue) for the data, resulting in structured table references which are preferable over direct cell references. Table references will expand and contract automatically with changing sizes of the data range.
- Hello, thank you for your answer, it helps me too.
burbigo3 Great! Glad I could help.
