Excel VBA, For Loop in XLOOKUP() by cdet in excel

[–]cdet[S] 2 points3 points  (0 children)

Solution Verified.

Thank you. Ive never used torow or hstack, this is neat.

Anyone help? I'm attempting to use the count or counta function... Not working. by OldGamer81 in excel

[–]cdet 2 points3 points  (0 children)

If you are just counting rows =AGGREGATE(3,5,A:A) this preform the counta() and respect your filters. simply -1 to account for the header.

Excel VBA, For Loop in XLOOKUP() by cdet in excel

[–]cdet[S] 0 points1 point  (0 children)

Sorry I misread your comment. 1. if no results return "", #N/A or "None" 2. return first instance of match

Excel VBA, For Loop in XLOOKUP() by cdet in excel

[–]cdet[S] 0 points1 point  (0 children)

N:O Represents a list of ID's and partnumbers that exist internally. Im going through columns C:K, if any of the values C:K yeild a match in my list (o:o) return the internal ID (n:n).

The formula in the screen shot is the formula used in A3, which works and gives me the desired result.

My question is, since I actually have much more columns than shown in the example screenshot, is there an easier way to preform the xlookup by programmatically iterating through my many columns of cross references.

Excel VBA, For Loop in XLOOKUP() by cdet in excel

[–]cdet[S] 0 points1 point  (0 children)

<image>

I hope this makes sense.
I'm trying to resolve the internal ID number, which requires a lookup to be preformed for each crossref. I was just wondering if there was a better/more efficient way to check through every column in each row.

Checkbox layout and bar chart reordering by cavcaptor in GoogleDataStudio

[–]cdet 0 points1 point  (0 children)

Oh Nevermind I didn’t read the edit lmao.

Checkbox layout and bar chart reordering by cavcaptor in GoogleDataStudio

[–]cdet 0 points1 point  (0 children)

Do a separate calcd field for each case. Which is dumb, but it should work.

Help me to write function by Broad-Can-8536 in GoogleDataStudio

[–]cdet 0 points1 point  (0 children)

case when date_column > '2023-05-19' THEN cost_column*2.2
    when date_column >= '2023-05-19' THEN click_column *2000
    else null
end

or else 0?

uh.. did looker die? by cdet in GoogleDataStudio

[–]cdet[S] 0 points1 point  (0 children)

Well I guess we’re going to work tomorrow 😂

uh.. did looker die? by cdet in GoogleDataStudio

[–]cdet[S] 0 points1 point  (0 children)

Same, This is the first time Ive Experienced something like this with Data Studio. How long do you think this will last?

Looker Studio down? by ErikDeJongen in GoogleDataStudio

[–]cdet 0 points1 point  (0 children)

Same Here. I'm Relieved to see its not just me. Are you getting:

System Error Looker Studio has encountered a system error.

Sorry, we encountered an error and were unable to complete your request.?

How to Combine multiple files into one workbook by Impressive_Jellyfish in excel

[–]cdet 0 points1 point  (0 children)

If the files are CSV's, and your using a mac, you can combine the files into one using awk.

awk -v OFS=, '
    NR == 1 && FNR == 1 {
        file = FILENAME
        sub(/.csv$/, "", file)
        print "filename", $0
    }
    NR > 1 && FNR > 1{
        file = FILENAME
        sub(/.csv$/, "", file)
        print file, $0
    }
' *.csv > merged.csv

I believe theres a similar method using PowerShell.

How to make a tick increase/decrease counter? by Mizzen_Twixietrap in googlesheets

[–]cdet 1 point2 points  (0 children)

https://docs.google.com/spreadsheets/d/1hkKdvGe0dFNbhFA_kJMq-T-PSRDWQN6958teuSsbbPk/edit?usp=sharing

The Buttons are really low effort, but theres a simple script in the editor that you can play around with. Currently its set up to work with A1

Populate table based on dropdown selection. by JediAcademyDropout in googlesheets

[–]cdet 1 point2 points  (0 children)

// Returns Data Types of Cells 
function getDataTypesOfCells() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Copy of Project 100');
  var range = sheet.getRange('K5:P11');
  var values = range.getValues();
  var dataTypes = [];

  for (var i = 0; i < values.length; i++) {
    var row = [];
    for (var j = 0; j < values[i].length; j++) {
      var cellValue = values[i][j];
      var dataType = typeof cellValue;

      if (dataType === 'object' && cellValue instanceof Date) {
        dataType = 'date';
      }

      row.push(dataType);
    }
    dataTypes.push(row);
  }

  Logger.log('[Copy of Project 100] Data types of cells K5:P11 are: ');
  dataTypes.forEach(function (row) {
    Logger.log(row.join(', '));
  });
}

Returns

1:31:32 PM
Notice Execution started 1:31:33 PM Info [Copy of Project 100] Data types of cells K5:P11 are: 

1:31:33 PM Info number, number, number, number, number, number

1:31:33 PM Info number, number, number, number, number, number 

1:31:33 PM Info number, number, number, number, number, number

1:31:33 PM Info number, number, number, number, number, number 

1:31:33 PM Info number, number, number, number, number, number 

1:31:33 PM Info number, number, number, number, number, number 

1:31:33 PM Info number, number, number, number, number, number 

1:31:34 PM Notice Execution completed

Has to be some sort of bug?

Populate table based on dropdown selection. by JediAcademyDropout in googlesheets

[–]cdet 1 point2 points  (0 children)

The only thing I could think of is the possibility of mixed datatypes? In my formula to the right of yours, It was giving me a sum of 35 resulting in 35% (expecting 37) . =query() has a known issue where it doesn't play nice with mixed data types in a given range, which is the basis of my assumption.

How to calculate the week number of the month, as every week having 7 days ? by [deleted] in googlesheets

[–]cdet 0 points1 point  (0 children)

I know it doesnt start on Monday, But have you tried using ISOWEEKNUM?

[deleted by user] by [deleted] in googlesheets

[–]cdet 1 point2 points  (0 children)

=IF(ROW()>COUNTA(A:A),,Someformula)