Excel_usecase

Help StudioX
,
1

Hi all,

I have 2sheets, sheet A and Sheet B, for Vlookup in excel-Sheet B i need unique id for that i need to concatenate multiple cells and Pivot the concatenated column so that if any duplicates that will be combined and will get desired output and now with this pivot output i have to lookup in Sheet A if it is found then that row should fill with Green color.

Sheet A

Name Date Shares Concetanate
A 6/30/2023 200 A30/06/2023 200
B 6/30/2023 300 B30/06/2023 300
C 6/30/2023 400 C30/06/2023 400

Sheet B

Name Date Shares Concatenate
X 6/30/2023 200 X30/06/2023 200
Y 6/30/2023 300 Y30/06/2023 300
C 6/30/2023 400 C30/06/2023 400

so in the above example column 4 is concatenated one using col 1,2,3 once done we need to create Pivot and with this Pivot will lookup in Sheet A if match found then should highlight in green In this case “C” is there in both sheets.

Thanks

2

@praneeth.v.c

You need not pivot or create a concatenate

you can follow the steps

  1. Use excel file
  2. For each row in excel (SheetA)
  3. inside use For each row in Excel(SheetB)
  4. Inside the second for each use if codnition with SheetAcurrentRow.ByField("Column1").StringValue = SheetBcurrentRow.ByField("Column1").StringValue and SheetAcurrentRow.ByField("Column2").StringValue = SheetBcurrentRow.ByField("Column2").StringValue and SheetAcurrentRow.ByField("Column3").StringValue = SheetBcurrentRow.ByField("Column3").StringValue
  5. On then side use format cell and give SheetAcurrentRow and in format select green color

Hope this helps

cheers

3

but @Anil_G in sheet B if have combination of 2 transactions which match transaction in sheet A then how to give that

4

@praneeth.v.c

It can match any number all the mactehs will be highlighted in green

Cheers

5

my entire sheetA is highlighting in green.

6

@praneeth.v.c

What did you give in format cell can you please show some screenshots

Also you used full all conditions in if?

Cheers

7

@Anil_G

image
image851×846 30.7 KB

image

8

@praneeth.v.c

Is the data same as show in the first message?

If so then only last tow would be highlighted with the given conditions

Cheers